Are you looking at a similar window and you desperately need access to your database?

You’re come to the right place, because there is a good way to recover access to the database without nasty tricks, third party software or losing any data.

The not so-good options

I had to recover a SQL Server database for a customer that changed its internal AD structure, so the database wasn’t accessible via Windows authentication anymore. The ‘sa’ password was known only to an employee that has left the company a while ago.

The most obvious method is to stop the SQL Server instance, install a new SQL Server instance and attach the databases to this instance. This issue has several drawbacks, because it takes a lot of time and you probably loose the master database. You can also use third-party tooling to recover or reset the sa password. I don’t like messing with third-party tools on my production servers, so I wouldn’t use this unless I had to.

Enter SQL Server maintenance mode

SQL Server has a maintenance mode, that allows you to access the SQL Server database with ‘sysadmin’ privileges without a password. You do need local administrator privileges on the SQL Server machine to enable it.

1. Start the ‘SQL Server Configuration Manager’ and select the SQL Server instance that you want to get access to.
2. Open the ‘properties’ and select the ‘Startup parameters’ tab. Add the ‘-m’ option here. Note that older SQL Server editions don’t have this tab, but you can access the startup parameters in the ‘Advanced tab’.
3. Restart the SQL Server instance (and keep the configuration manager open).
4. Open a command prompt and run one of the following command: sqlcmd.exe. If everything is fine you now have access to your SQL Server instance with ‘sysadmin’ privileges.
5. Run one of the following commands to restore access in non-maintenance mode (choose the one that is most applicable to your situation):
   • EXEC sp_password NULL, '<password>', 'sa' (restore your sa password, only when SQL Server authentication is active).
   • EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin' (add sysadmin privileges to the specified Windows account).
6. Always make sure you enter GO on a new line to make sure the command is executed. If you have completed these steps, then you should have access to your SQL Server database again.
7. Go back to the SQL Server configuration manager and remove the ‘-m’ option.
8. Restart your SQL Server instance.

Now you should be able to log back in. Hope this helped…