Blog: SA

The sa account for the SQL Instance on a number of PCs got locked out. One way to unlock all sa accounts is by putting the instance in single-user mode and creating a recovery account, then you can get connected to the SQL instance with that recovery account and unlock the sa account.

Here are the instructions for how to do that: [more]

1. Open SQL Server Configuration manager and Stop the SQL service. 
2. Once the service stops, right-click the service and select properties. 
   1. Got to the Advanced tab, find the Startup Parameters and add –m; to the front of the line (This will start the SQL Service in Single-User mode the next time it starts up) 
3. Then expand SQL Server Network Configuration, and click on Protocols for your SQL instance. 
   1. Then right-click Named Pipes, go to properties, and change the Pipe Name to just \\.\pipe\sql\query (This will allow you to connect to the SQL instance via sqlcmd) 
4. Then start the SQL Server service again. 
5. Once the service has started open up a Command Prompt on the PC and type sqlcmd. 
   1. At the 1> Prompt, enter CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret1’ and press ENTER (Where TopSecret1 is the password you would like to use for the recovery account) 
   2. At the 2> Prompt, enter Go and press ENTER 
   3. At the next 1> Prompt, enter sp_addsrvrolemember ‘recovery’,’sysadmin’ and press ENTER 
   4. At the 2> Prompt, enter go and press ENTER 
6. After creating the recovery account, go back into SQL Server Configuration Manager, stop the manager and undo the changes you previously made to the Startup Parameters and the Named Pipes configuration. (Remove –m; from Startup Parameters for SQL Server services and change Pipe Name back to what it was originally) 
7. Then login to a PC or Server that has Microsoft SQL Server Management Studio installed and connect to the SQL Instance on that PC with the recovery account you just created. 
8. Once you’re connected to the SQL Instance, go to Security -> Logins, then right-click the sa account and select Properties.  In the Properties screen, click Status on the left.  Then uncheck the box for Login is locked out then click OK. You will get a prompt that the Password needs to be reset, so click General on the left. For Password and Confirm Password enter the password you’d like to use and click OK. 
9. Go back to the SQL Server Configuration Manager on the PC and restart the SQL Server service. 
10. After restarting the services you should now be able to access the DB with the sa account. 
11. After verifying that everything is working correctly, log back into the SQL Instance through SQL Management Studio, but this time use the SA account you unlocked. Go to Security -> Logins and delete the recovery account you created earlier.