Help! I am Locked Out of my SQL Server Instance

Beginning with SQL Server 2008, local administrators are no longer SQL Server sysadmins by default. Microsoft removed this feature in an attempt to increase security. I agree with the change. The principle of least privilege would imply that even system administrators who handle patching and management of Windows do not require access to the data which resides on the server.

Unfortunately, the change makes it easier for a DBA to accidentally lock himself/herself out of the instance. By removing the login or forgetting the sa password, you will need to regain access of your locked out instance withe more invasive measures than before.

Downtime

Straight out of books online, there are two supported methods for accessing a locked out SQL Server instance. Both of these methods require downtime of the instance.

The first method is to reinstall the SQL Server instance. I do not recommend this method because of how invasive it is. The high-level procedure is as follows.

1. Uninstall the existing instance.
2. Install new instance.
3. Attach all of the user databases which were previously attached to the old instance.
4. Recreate system objects which are now missing.
   1. You could restore the master database from backup but you would need to restore a version which is older than the lock issue.

The second and recommended method is to run SQL Server in single user mode. When in single user mode, a local administrator has implicit sysadmin access to the instance.

1. Stop the SQL Agent service.
2. Open the SQL Server Configuration Manager.
3. Select SQL Server Services.
4. Right-click on the SQL Server service and select Properties.
5. Add the -m (or -f) startup parameter on the Startup Parameter tab.
6. Accept the change.
7. Restart the SQL Server service.
8. Log into the server with a local administrator account. I recommend using sqlcmd.exe to prevent the client from trying to open multiple connections.
9. Recreate the sysadmin account / grant appropriate permissions.
10. Close the client.
11. Remove the single user mode startup parameter.
12. Restart the SQL Server service.
13. Start the SQL Agent service (if desired).

Use dbatools’ Reset-SqlAdmin cmdlet to have this taken care of for you by PowerShell.

No downtime

For when your system requires extremely high availability, such as 99.9999% up-time, there is a method which can be used without shutting down SQL Server. Check out Aaron Bertrand’s (t | b) article on Recover access to a SQL Server instance for details on that method.