In this post I discuss how to restore access to a SQL Server 2012 database if you get locked out of it.
So as you can probably expect for a Monday afternoon I realized that one of the installations of SQL Server 2012 that I have on a cloud VM was busted. It wouldn’t let me or anyone else login to the SQL Server instance. So much so that even the sa password was not accepted!
This was slightly worrying as we were basing quite a lot of testing onto this box and we simply could not afford to lose the current state of our DB.
While eventually I was able to get into the SQL Server installation it wasn’t very straightforward. Well not until I had figured it out. I wanted to write this quick tutorial to help others should they find themselves in the same situation. You would need admin login privileges on the machine that runs your SQL Server Installation.
- Log onto the machine with the account that has admin level privileges on the machine.
- Open up SQL Configuration Manager and stop the running SQL Server and SQL Agent services
- Launch a command prompt with administrative privileges and navigate to the Binn (note double n) folder of your SQL Server 2012 machine. Usually the Binn folder is located at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
- Start the SQL Server Database Engine with minimal configuration or under forced configuration by using either
- SQLServr.exe -m or SQLServr.exe -f
Do not close this command prompt for the duration of this exercise. Let it run.
Next we are going to create a new login for SQL Server and add that to the Sysadmin role. Start again by opening up a command prompt in Admin mode
- Open up a new command prompt in admin mode and use the SQLCMD command to connect to the running instance of SQL Server
SQLCMD -S <Server_Name\Instance_Name>
Example: SQLCMD -S VM-XYZ-ABC
You will now be logged into the instance of SQL Server. Beware as you are logged in as Admin on this SQL Server!
- Now that we are logged into the SQL Server instance as an admin we can create a new login and add that login to the SQL Server SYSAdmin role. Start with this
CREATE LOGIN '<Login Name>' WITH PASSWORD = '<Password>' GO
Once the new login has been created you can easily add it to the SYSADMIN Group as
At this point you can exit out of this command prompt. Go back to the first command prompt you had running and press Ctrl-C to stop the SQL Server Engine which is running with minimal or forced configuration. Start the SQL Services as normal using either the SQL Server Configuration Manager or by firing SQLServr.exe without any arguments.
Fire up SQL Server Management Studio and use the newly created login id to login to SQL Server instance. As you are the system admin now with this login you can fix whatever problems prevented your other logins from working properly.