Wednesday, July 16, 2014

SQL Server 2005 Forgot SA Password, How to Retrieve?

When forgot SA password and failed to login to SQL Server what can you do? As you know, the SA account is the super administrator account, without which you will have no privilege to create database in SQL Server. If forgot SA password, how can you retrieve the SA password to unlock the SA account and login to SQL Server with SQL Server Authentication.  To retrieve lost SA password, firstly look at the 2 situations below and see which situation you are in.

Situation 1: Forgot SQL Server SA password but still can login with Windows Authentication
Situation 2: Forgot SQL Server SA password and failed to login with Windows Authentication

If you are in the first situation you just need to login to SQL Server with Windows Authentication and change SA password. If you are in the second situation, should it be the only option to reinstall SQL Server? Of course not, you still can reset SA password without any login. Now take some minutes to browse through this article to see how to retrieve forgotten SA password on SQL Server 2005. If you are using SQL Server 2008/2012/2014, the methods will also perfectly work.

Aiming at the 2 situations above you will have 2 options to retrieve the SA password.

Option 1: Retrieve SA password by Windows Authentication
Option 2: Retrieve SA password by SQL Password Geeker

Option 1: Change SA password by Windows Authentication

When forgot SA password but you still can login to SQL Server with Windows Authentication, then you can change the SA password.

Step 1: Open the SQL Server Management Studio. Log in to with Windows Authentication.

Step 2: Click on New Query on the tool bar.



Step 3: Type sp_password Null, 'NewPassword', 'sa' on the blank space in Query window. Then click on Execute. It appears a message saying "Command completed successfully" which means your SA password has been changed successfully.




Tips: In the command sp_password Null, 'NewPassword', 'sa', the "NewPassword" is your new sa password and "Null" means your forgotten sa password. And you have executed this command to retrieve a new password.


Option 2: Retrieve SA password by SQL Password Geeker


When forgot SA password and failed to login to SQL Server with Windows Authentication, use SQL Password Geeker to reset the SA password.

Step 1: Stop SQL Server services.

Step 2: Download, install and run SQL Password Geeker.
Step 3: Click on Open File button. Navigate to the disk where your SQL Server is installed. And go to Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf. Select the master.mdf file and click on Open button to import the file to SQL Password Geeker.




Step 4: Select the sa user and then click on Reset button.




Step 5: Type a new password for your sa account. Click OK to finish. A dialog saying "password changed successfully" will pop up to tell you have successfully reset the sa password.

By the steps above you have retrieved a new sa password. Then do not forget to restart SQL Server services and log in to SQL Server by the new sa password.