If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode.
1.Enable SQL Single-User Mode
- Open SQL Server Configuration Manager
- Stop the SQL Server Instance you need to recover the SA password
- Open the properties on the SQL Server Instance and click on the Advanced tab
- Change the Startup parameter by adding -m; at the begging of the line and click OK
- Start the SQL Service Instance
2.Use SQLCMD to add a Sysadmin Account
- Open the command prompt
- Run sqlcmd and press enter
- Run a Transact-SQL (Below) command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add. *NOTE: if you receive errors, I’ve listed some common issues at the end of this article.
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';
GO
3.Enable Multi user mode
- Open SQL Server Configuration Manager
- Stop the SQL Server Instance
- Open the properties on the SQL Server Instance and click on the Advanced tab
- Change the Startup parameter by removing the -m; at the begging of the line and click OK
- Start the SQL Service Instance
4.Reset sa Password
- Open Microsoft SQL Server Management Studio and login with the account you added
- Under the DB, expand Security, then Logins
- Open the properties for the sa account, and reset the password
