If an SA password is ever lost then instead of re-installing SQL and re-attaching the DB, SQL Server provides a great disaster recovery method that keeps objects and data in the master DB. Members of the server’s local Administrators group can reach SQL Server by starting it in single-user mode.
In this blog we will show how to recover SA password on Microsoft SQL server by starting SQL Server in single user mode when you forget “sa” credentials.
1. Microsoft SQL server configured with mixed (Windows and SQL).
2. User should be administrator of server.
1. Open the SQL Server Configuration manager.
2. Go to SQL Server Services and Stop the SQL Server Instance.
3. Right click on SQL Server Instance and select the Properties option.
4. Go to Startup Parameters tab, type –m to Specify a startup parameter and click on Add
5. Click on Apply and then OK.
Note- If SQL server is before 2014 we could not change startup parameter but you can check the start parameter go to Advanced and check the startup parameter.
6. Click OK on Warning which prompts after apply the changes in Startup Parameter.
7. Now, Start SQL Server Instance.
8. Open the command prompt.
9. Run the command EXEC sp_addsrvrolemember ‘DOMAIN\Username (Domain username) ‘, ‘sysadmin’;
10. Run the command Go.
11. Now go to SQL Server Instance clicks on Properties.
12. Go to Startup Parameters tab and remove startup parameter –m from Existing parameters after that click on Apply then OK.
13. Now stop the SQL instance again.
14. Now Open the Microsoft SQL server management.
15. Use the same account which has been added above to login.
16. Now expand the SQL server > Security > Logins and open the properties to reset SA account password.
17. The login account added is a SQL sys admin now, so it can reset the SA password.