Do you see things a little differently?

Thursday, September 5, 2013

SQL Server with a lost SA password, and no other Admins. What to do....

I'm not a DBA, I just play one at work...
Another instance of playing the DBA today. Discovered a SQL Express instance at a remote site with critical maintenance data which was not being backed up. I attempted to login, but found myself limited to a public role. We didn't know the SA account password, and the person who set it up is no longer with the company. To fix this do the following...

  1. From SQL Server Configuration Manager, select the SQL Server Services and then right click on your SQL Server instance, in this case "SQL Server (SQLEXPRESS)". Choose properties and select the Advanced tab.

Add ;-m to the end of the Startup Parameters as shown, and restart SQL Server service. You will now be in Single User mode. If you connect to SQL in single user mode as the local server administrator, you will have administrative access to SQL Server.

Using SQL Server Management Studio, connect to the instance and run the following TSQL code to add the local administrator account to the SQL sysadmin role. Remember to replace server with the name of your system.

EXEC sp_addsrvrolemember 'server\Administrator', 'sysadmin';

When this successfully completes, go back to the Startup Parameters and remove ;-m to set it back normal mode. Restart the SQL Server service and you should be able to connect to SQL as sysadmin using the local Administrator account.

I was able to get in and add our SQL Admin group, and now the Backup Admin can perform backups on the database. Crisis Averted. :)