Deleted all logins but SA

Question

Deleted all logins but SA

Kenneth.Fisher

SSC-Insane

Points: 20396
More actions
September 21, 2007 at 7:33 am

#374562

Ok I was trying to clean up a new (test) install of 2005 so that we could take a snapshot of the virtual server to be copied for some new servers. I decided I should clean up the security because its easier to add a bunch of new stuff .. copied from our 2000 servers .. than try to clean all the old stuff up on each server. Well I got a little carried away and as an upshot the only logins left are SA and my login .. that no longer has any rights.
We are in the process of looking for the SA password .. but assuming I can't find it does anyone have any suggestions? Other than re-installing of course?
Thanks
Kenneth Fisher
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Did you remove builtin admins as well? If not, windows authentication will get you in. I presume you don't know the sa password.

I've never tested this, but from what I've heard, if you shutdown SQL and restart it in single user mode (check BoL) then local administrators will have sysadmin permission, regardless of logins.

Of course, to do that, you need to be a local admin on the server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J DEYHLE Valued Member Points: 52 More actions · Answer 2

In the future, you may want to consider using a free tool from Idera called SQLpermissions. It will create a T-SQL script for you that you can use to automatically move SQL logons and permissions from one server to another. You can download it at http://www.idera.com/freetools

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

Actually we are planing to do just that. My problem here was that I had already moved over all of the logins from my dev server during testing. I was removing all of the logins in preperation of making a copy of the server (its a virtual server) to be used in our testing and staging enviornments.

Have you had any experience with SQL Secure? We are looking into getting a license for it.

In the end we are probably going to uninstall and reinstall so we can change the instance name. Unless anyone knows a way to change the instance name on an installed server? The Database Engine instance that is.

Thanks for all of the help and advice.

Kenneth Fisher

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]