How can I recover my sa Password?

How can I recover my sa Password?

Q: I recently ran across a case where a company I worked with had lost their sa password. How can I reset the sa password? Do I need to reinstall SQL Server?
A: No. In most cases you won’t have to reinstall 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 that looks a lot like Figure 1.

Figure 1 – Connecting with Windows Authentication

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 like you can see in Figure 2.

Figure 2 – Changing the sa password with SSMS

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 2005 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 like you can see in Figure 3.

Figure 3 – Setting SQL Server to start in single user mode

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.