Home / server / Questions / 214347
Accepted
Shiraz Bhaiji
Asked: 2010-12-21 01:45:20 +0800 CST

Access to SQL Server when administrator account deleted

  • 772

An interesting situation here.

We have a database server, used for testing only, where someone went in and deleted the administrator login.

Since this is a test server the was no other admin level login on the server.

Is there a way to get access to the server again without reinstalling SQL Server?

We do not need the data in the databases, these are droped and recreated everytime the tests are run.

sql-server-2008 login database-administration

3 Answers

  1. I realize this is a little bit old, but I found a workaround today that worked for me and thought it might be helpful to others.

    If your NT Authority/System account has admin access, you can use the following trick to log in as the NT Authority/System account and create a new login/reset credentials on other logins.

    Steps:

    1. Download PsExec: http://technet.microsoft.com/en-us/sysinternals/bb897553. This will allow you to run a program as the system user.
    2. Extract the Zip and copy everything to your executable path
    3. Open a command prompt and type the following: psexec /s /i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

    That should open up SQL Server Management Studio as the NT Authority/System login.

    This also shows why it is probably a good idea to limit access to the NT Authority/System account unless you've got some other good reason.

    • 4
  2. Best Answer

    The simplest solution might well be to reinstall SQL Server. Don't forget that you need to consider the time (and hence money) it will take to look for and try alternate solutions which may or may not work.

    Though the first thing I'd try is a "repair" installation, to see if it covers this eventuality.

    • 3

  3. The best solution would have been to use the dedicated admin connection (DAC) which will let any administrator of the windows machine access the SQL instance. Unless you have absolutely no way to log in as the built-in windows administrator account or any account that is a member of the local Administrators group you would probably need to do a full reinstall of both Windows and SQL to gain access again unless the sa account (or some other SQL login that was assigned the sysadmin fixed role) was available. A SQL login doesn't help with your lack of administrator access to the Windows machine itself though... if that is what you meant anyway.

    http://msdn.microsoft.com/en-us/library/ms189595.aspx

    • 1