Recover SQL Server Password Using Different Methods

In order to Recover SQL Server Password, we should first understand that SQL server is a database that is used for storing database and retrieving data from it (whenever required). Although, the server administrator has permission to create any number of users with a unique username and same password.

The user can change this password, individually if they desire. The server stores this user password in a hash format within a master.mdf file. Now consider the following scenario:

“A server user has forgotten the password of his SQL server due to which he is unable to access or operate the server. Now he is in a big trouble because he does not know any way to recover SQL server password and has a query that ‘how to recover MS SQL Server SA password ?”

If the user is not an admin, then they can ask SA (system administrator) to reset the server password. However, if the user is the system administrator, then they will either have to go for certain manual tricks or use the third-party product for recovering SQL server password. Here you will find possible solutions to recover SQL server password using different methods.

Manual Methods To Recover The SQL Server Password

Password Recovery Using Different Commands
This method requires command prompt window of the server where different commands to recover password can be executed. Go through following the set of steps to perform password recovery:

    • Open the command prompt window of the server and execute osql -L command
    • Type following command by mentioning full server name in section and then executing the command: OSQL -S -E
    • Run following query for inserting a new password. Mention a new password in the portion sp_password NULL, ”, ‘sa’

    • Now your password is change and administrator has found a new password for the server. Recover SQL Server Password Using SSMS Window
    • Go to Start >> All Programs >> Microsoft SQL Server 2005 >> SQL Server Management Studio

NOTE: Instead of Microsoft SQL server 2005, you can find Microsoft SQL server 2008, Microsoft SQL server 2010, or Microsoft SQL server 2012

    • Through Windows Authentication, connect to your SQL server instance
    • Under Security, go to Login object and expand it for once by right-clicking on SA users & selecting Properties.
    • Now, mention the new password in Password text box and re-enter the same in Confirm Password text box and then click on OK button

Recover SQL Server Password Using SQL Script

    • Launch SQL Server management studio application on your server machine
    • Now open a new query in the server management studio and execute following script

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

NOTE: Mention a new password in required field so as to create the new server password
Recover SQL Server Password With Disabled SA

It is a hit-and-trial method when SA is disabled and the server can be started in single user mode. Go through the following steps to perform the same:

    • Disable SA from the server
    • Type sqlcmd -M command in command prompt window of the server
    • Right-click on the cmd file and choose Run as Administrator option
    • Now its time to select the sysadmin and then reset the password

Third-Party Software To Recover SQL Server Password
Another approach for resetting the password of the server, there is a third-party application with help of which one can recover the password within just a few couple of clicks. The working of the product is given below:

    • Browse the master.mdf file, which stores all username and password of the server and then click on Open to proceed
    • Scanning of the selected master.mdf database file will take place and then the tool will display scanning completion report after its completion. Click on OK button
    • You will find a users list, which were created in the server by admin. There will be one of the two following options in the password column of each user:
      1. Unknown: It indicates that your database is encrypted and have a password within it
      2. Empty: Your database file is not secured with password and hence, you can login into your account without any password.

Highlight the username from which you want to reset the password and then click on Reset button from the current window pane

    • After resetting the server password, you will encounter a dialog box which indicate that password of the chosen name has been reset successfully

Observational Verdict

After discussing various methods to recover SQL Server password one can conclude with the fact that it is not a tough procedure to reset password in server. In this blog, we have discussed all the manual procedure of password recovery on administrator level.

On the other hand, the third-party tool requires the only master.mdf file to remove password from SQL database. It is recommended to users to use the third-party software because it consumes less time and does not require any type of commands execution.

You might also like More from author