How to login to sql server after all admin accounts removed and you are locked

Recently I had an interesting situation. Consider below situation

• You want to connect to sql server as sysadmin but you are not added to sql server logins using windows authentication
• BUILTIN\Administrators group has been removed from SQL Server
• You have no way to login to sql server because all admin accounts have been removed or deleted

When you face total sql server lockout situation in sql server perform the following steps.

1. Stop SQL Server Service and SQL Agent service

2. Open a cmd prompt window (Start -> Run -> type cmd and press the ‘OK’ button) and navigate to SQL Server’s Binn directory as shown below.

3. Once you are in SQL Server’s Binn directory type ‘sqlservr -m’ command to start SQL Server in single user mode.

sqlservr.exe -m

4. Once SQL Server is started in single user mode open another command prompt and type below command.

sqlcmd -E

Note that I used -E switch for trusted mode and didn’t specify any server name so it will by default connect to localhost. If you have named instance then use -S for servername, -U for userid and -P for password. For more help type sqlcmd /?

5. Once you are connected to sql server using sqlcmd you can add yourself back to sql login and then make yourself sysadmin to take full control of the instance.

> CREATE LOGIN [MyDomain\SomeUserName] FROM WINDOWS
> GO

If you have Mixed Mode enabled then you may try something like below too

> CREATE LOGIN TempUser1 WITH PASSWORD = 'MyPassWord@1234'
> GO

6. Once login is created you have to add that login to sysadmin role using below command.

> exec sp_addsrvrolemember [MyDomain\SomeUserName],[sysadmin]
> GO

or

> exec sp_addsrvrolemember [TempUser1],[sysadmin]
> GO

7. Go back to our first window where we launched sqlserver using -m switch. Type Ctrl+C and it will ask you stop sqlserver. Just type Y

8. Restart SQL Server from Services and try to connect using SSMS…… That’s it. You should be able to connect to sql server now

Hope this will save you some time and make you feel home.