If you have every found yourself locked out of SQL Server 2008 R2 it can be quite a scary experience. This scenario does happen from time to time even with our best intentions to follow Microsoft best practices. Over time accounts can be removed from Active directory that were once administrators or maybe we simply have forgotten the SA password when SQL was installed.
Don't panic, no need to try and hack into SQL as Microsoft has given us a back door to access SQL and create ourselves a new administrator.
The first thing we need to do is notify our users that the systems that utilize that particular SQL Server will be down for a bit while we fix our little administration problem (Assuming that they are still up and running!).
Once this is done we need to log into the SQL Server as an administrator using a local account on the server. This is very important as the procedure below will not work with a domain user that has local administrator rights on the server. If possible just use the '[server name]\administrator' account.
Now you can begin by stopping the SQL Server and the SQL Server Agent services from the SQL Server Configuration Manager:
Then we need to re-start the SQL Server service in single user mode. The easiest way is to modify the start-up parameters on the advanced tab from the SQL Server Configuration utility as shown below (Don't forget the semi-colon or add and spaces!). To access the parameters, right click on the SQL Server service in configuration manager and select 'Properties' then click on the 'Advanced' tab.
Once the parameter has been modified, start the service from the configuration manager. The SQLCMD utility can be used as well (SQLCMD -S [server/instance] -m).
This will give us access to the backdoor Microsoft has provided. When the database server is running in single user mode a local administrator will be able to open Management Studio and will automatically be logged in as a server administrator.
Now we will be able to create a new administrator or add the server role to an existing login.
Phew! Crisis over!
Once the new administrator has been set up, remove the single user start-up parameter and restart the service and you should be back in business.
