Recover SQL sa password

Issue #

In SQL Server there may be cases in which you have lost your sa password or the sa user logons are not working. This articles sheds some light in recovering the SQL sa password for SQL Server.

Scenario 1 #

You know the sa password but sa logons are not working. This means that SQL mixed mode authentication has been disabled or the sa password has been disabled. In order to enable the SQL sa password, run the SQLCMD command in Windows CLI:

ALTER LOGIN sa enable
GO

Scenario 2 #

You know the password of at least one Windows AD-based sysadmin user but you have lost the password of the SQL sa account. In this case, you can simply logon to the SQL Server Management Studio, navigate to Security --_ Logins, right click the sa account and in the security properties simply reset the sa password and click OK.

Scenario 3 #

You must start SQL Server in single user mode by adding the parameter -f or -m in the startup parameters. Launch SQL Server Configuration Manager and select the applicable service of the SQL Server instance. Right-click and click on the Properties option. After adding the startup parameter, click on the Apply button and then the OK button in the warning message window.

Restart the SQL Server service to start SQL Server in single user mode. When the SQL Server is started in single user mode, all the users which are members of the host’s local administrator group can connect to SQL Server instance and gain the privileges of the sysadmin server level role which helps us to recover SA password. So, if you are a member of the local administrator group, you can connect to SQL Server instance using SQLCMD or SQL Server Management Studio.

If you do not know the sa”login password, create a SQL server login using the below T-SQL script.

CREATE LOGIN SaNew WITH PASSWORD = 'Abc321789';

Add the login SaNew to the server level role sysadmin. Please use the following T-SQL script to add the SQL Server login to the server level role sysadmin.

ALTER SERVER ROLE sysadmin ADD MEMBER NewSA GO

Similar commands can be executed for granting administrator access to Windows AD accounts or AD groups for SQL Server logins, such as in the examples below.

--Example1: 
CREATE LOGIN [domainname\username] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [domainname\username]
GO

--Example 2: 
CREATE LOGIN [domainname\groupname] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [domainname\groupname]
GO