How To Recover "SA" Password in Microsoft SQL Server

If you have access to the Windows Server administrative password and Windows Authentication is enabled you can easily reset the sa password using SSMS. Simply login into the host Windows Server as Administrator then open SSMS and connect to the Database Engine using Windows Authentication. You’ll see a dialog.

After connecting you can either use SSMS or T-SQL to change the sa password. To use SSMS navigate to the Security node and then expand Logins and right click the sa login to change the Password properties.

Alternatively you can select New Query from the SSMS menu to open Query Editor and then run the following T-SQL query to reset the password for the sa login.

GO

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassw0rd'
GO

That’s all pretty easy but what if you don’t have access an account that isn’t included in the sysadmin role? Fortunately, beginning with SQL Server members of the Windows Administrators group can access SQL Server in single-user mode. This allows you to add a login to the sysadmin role which you can then use to start SQL Server with administrative rights. The account needs to be a member of the local administrators group.

To start SQL Server in single-user mode add the parameter -m at the command line. The easiest way to do this is to use Configuration Manager. Stop the SQL Server Instance you want to change. Right click the instance to open the Properties dialog and click the Startup Parameters tab. Enter  –m in the Startup parameters option.

NOTE: Sometimes SQL Server Configuration manager won't appear in search, either due to it ,it was installed recently ,so it was not indexed or any reason.  So you can use following commands as per your installed SQL Version.

• SQLServerManager13.msc   SQLServer2016

• SQLServerManager12.msc   SQLServer2014

• SQLServerManager11.msc   SQLServer2012

• SQLServerManager10.msc   SQLServer2008

Next, start the SQL Server Instance. Then open an elevated command prompt and enter sqlcmd. In the sqlcmd windows enter a command like you see following to add your login to the sysadmin group.

EXEC sp_addsrvrolemember 'CONTOSO\mikeo2', 'sysadmin';
GO

This example adds the account CONTOSO\mikeo2 to the SQL Server sysadmin role. Next, use Configuration Manager to stop the SQL Server services and remove the -m from the Startup Parameters. Then restart the SQL Server service. You should be able to login with sysadmin rights using the account you added. Then you change the sa password using one of the techniques presented earlier.