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.
- Start the ‘SQL Server Configuration Manager’ and select the SQL Server instance that you want to get access to.
- 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’.
- Restart the SQL Server instance (and keep the configuration manager open).
- 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.
- 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).
- Always make sure you enter
GOon 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.
- Go back to the SQL Server configuration manager and remove the ‘-m’ option.
- Restart your SQL Server instance.
Now you should be able to log back in. Hope this helped…