Locked Out Of SQL Server
Has there been a time when you needed to get into a SQL Server to perform a sysadmin function?...
This same situation happened to me the other day. For whatever reason the DBA team got locked out of a SQL Server in the production environment. The DBAs could RDP to the server as our domain group belonged to the local Administrators group but we could not Windows Authenticate into SQL Server. Of course here you would say..."just sign in using the SA login". We were saying the same thing... however, none of us knew the password as this SQL Server was legacy. So what to do... after researching this is what we came up with...
I went ahead and put the SQL Server into Single User Mode. This is how you do this...
Go to your Start menu -> All Programs -> Microsoft SQL Server 200x -> Configuration Tools -> SQL Server Configuration Manager. Once you are here, on the left side click on SQL Server Services and on the right side select SQL Server (MSSQLServer) and right-click and select Properties and click on the Advanced tab. Once you are here look down the list and find Startup Parameters. In the very beginning of the parameters put -m; (enter it just as you see it with the semi colon right after the m (m represents Single User Mode). After clicking 'Apply' the following message box appears:
You are now in SQL Server as a sysadmin. Connect to your SQL Server through SSMS and expand your server tree. Select Security and then Logins... here you will be able to make changes to the accounts and grant the correct \ necessary permissions.
Now that you have made the changes to your security in SQL Server just follow the steps in reverse order to take SQL Server out of Single User Mode (by removing the -m in the Startup Parameters) and restart the SQL Server and SQL Server Agent services.
I hope this helps...
Until next time...
Crazed DBA
