SQL server: A database used to archive and restore the data, which works on transact – SQL commands. An administrator is having permission to discover any number of end users with an individual and unique id. Initially, the password of all the SQL server users is same and a single person is having right to change his/her password. Well, this security SQL Server password is saved in the master.mdf file in form of hash value.
Well, if in case this password is forgotten by an individual then, he/she needs to have a conversation with his/her administrator. It is so because the SQL system administrator will be having right to reset forgotten password of that individual. But, what if the administrator himself forgot the password? Therefore, in this blog, one is going to learn solutions on how to reset SQL server password, manually.
Forgotten SQL Server Password: Let’s Recover It
Here, users are going to find solution in both scenarios where on the one hand the administrator is knowing the password and on the other hand, admin had forgotten the password. So, Let’s begin.
Scenario 1: SQL Server User Reset Password
Method 1: With Help of SQL Server Agent Service
- On your machine, go to Start >> ALL Programs >> Microsoft SQL Server >> Configuration Tools and click on SQL Server Configuration Manager
- Hit the mouse button on SQL Server Services from the current screen of manager
- Right-click on the SQL Server Agent and then click on Properties option
- From the Properties dialog box, click on Log On tab, fill up the text box of Password and Confirm Password
- Click OK to finalize the changes made in SQL server user’s password
Method 2: With Help of SQL Server Configuration Manager
- Open SQL Server configuration manager, as you did in earlier method and go to SQL server services
- Right-click on SQL Server (nameofinstance) and choose Properties option from the displayed list
- Click on Log On tab and enter characters in Password and Confirm Password text box
- At last, click on OK button to update the changes
Note: Without restarting the SQL server, all updates will be made in master.mdf file of the server. However, you may utilize resources of the SQL server in offline mode, on SQL server clustered instance and then, restart the machine as well.
Scenario 2: SQL Server SA Reset password
The procedure described till now were having use of SQL Server configuration manager wizard. But, if the administrator is not knowing the password then, he/she needs to make use of master.mdf file. This file is also managed by SQL SA therefore, admin needs to track this file on local machine first and then, proceed with further steps.
Important Point: SQL Server services must be stopped for preventing the procedure from blocking in between
Method 1: With Help of SSMS Management Console
- Launch the management console program, which you will find while clicking on Start >> All Programs >> Microsoft SQL Server >> SQL Server Management Studio Express
- Use Windows Authentication mode to enter into the SQL server
- Under Object Explorer, open the Security folder that is located in Logins folder and then right-click on SA account to go to Properties window
- Enter a secured text in front of Password and Confirm Password field
- At last, click OK to update the changes
Method 2: With Help of A SQL Script
- Launch SQL Server Management Studio and open a new query window
- Execute the following query on current screen
Time to Wrap Up With Final Words
After implementing manual approaches on How to reset SQL server password, it might be possible that system administrator is unable to login to his or her account. Therefore, users are now having only one solution left to resolve the problem i.e., third-party utility. One such tool is SQL Password Recovery Software, which is having astonishing features to reset password of the master.mdf file. The product is capable of resetting both empty as well as unknown password of the SQL file. Hence, don’t be tensed if you have forgotten your SQL password. Users can either use manual approaches described on this blog or use SQL password recovery software to overcome their problem.