Ranga Babu
Recover SA password.connect SQL Server using windows authentication.

Recover a lost SA password

September 20, 2019 by

In this article, we will review how to recover SA password by starting SQL Server in single user mode when you forget “sa” credentials or lose the access to the instances due to the following reasons:

  • You configured a new SQL Server instance and lost or forgot the “sa” password
  • All the users who are part of sysadmin server level role are removed accidentally
  • All the users who are part of sysadmin server level role are disabled accidentally
  • Lost sysadmin access to the SQL Server instance

Consider this scenario:

Initially, I was a member of the [BUILTIN\Administrators] group. To illustrate this scenario, I connected to SQL Server using Windows authentication and I deleted all the logins in the SQL Server instance. Please refer to the below image that shows all the logins that were deleted and the login “sa” is disabled.

recover SA password by starting SQL Server in single user mode

I closed the connection and tried to connect the SQL Server instance using the same account which I used earlier. I am unable to log in as I deleted all the logins and the group [BUILTIN\Administrators] from which I gained access earlier. Please refer to the below image that shows login failure error.

login failed.

Now, I’ve lost access to the SQL Server instance. I do not have any login that can connect to the SQL Server instance. I do not know the “sa” login password or even if the “sa” login is disabled.

In this situation, to gain access to your databases, we can re-install the SQL Server instance and attach the databases. This process may take more time and effort.

To gain access to the SQL Server instance back or recover SA password, please follow the below steps.

Start SQL Server in single user mode

We need to start SQL Server in single user mode by adding the parameter -m or –f in the startup parameters. Open SQL Server configuration manager and select the service of SQL Server instance. Right-click and click on the Properties option.

start up parameters to run SQL Server in single user mode

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 who are a member of the host’s local administrator group can connect to SQL Server instance and they will gain the privileges of server level role sysadmin 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. In this case, I am using SQLCMD.

Launch the Command Prompt and connect to SQL Server using SQLCMD. You would be able to successfully connect to the SQL Server instance.

connect SQL Server using SQLCMD

Now, if you know the password of “sa” login, just enable the “sa” login and start SQL Server in multi-user mode, connect to SQL Server instance using “sa” login and create whatever logins you need for your application to run.

Please refer to the below T-SQL script to enable “sa” login.

connect to SQL Server in single user mode to recover SA password

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

Add the login NewSA 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.

create sql server logins

Remove the startup parameter -m or -f that is added and restart the SQL Server services. Now, SQL Server instance is started in the multi-user mode and has the login that you created above. Please refer to the below image that shows the connection is established using NewSA login which is created above.

recover SA password

In some cases, you may have not enabled mixed-mode authentication (i.e. SQL Server and Windows authentication mode). In this situation even though we create a SQL Server login, it does not allow us to log in to SQL Server and throws login failed error.

Create a login from Windows account. Execute the following T-SQL script to create a Windows authentication login in SQL Server.

Execute the following T-SQL script to add Windows authentication login to server level role sysadmin:

Replace the computer name and the login name with yours.

connect to SQL Server in single user mode from windows authentication

Remove the startup parameter and restart the SQL Server service. Log in to the computer with the same user as above.

Open SQL Server Management Studio and connect to the SQL Server instance using Windows authentication.

Recover SA password.connect SQL Server using windows authentication.

In case you want to create a login from Windows group, execute the following T-SQL script.

Conclusion

In this article, we explored how to gain access to SQL server instance or recover SA password when you forget it by starting SQL Server in single user mode. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu

Latest posts by Ranga Babu (see all)

718 Views