SQL Server is a database which is used to store and retrieve the data. It runs on transact – SQL. In this the SQL administrator has a right to create a number of users with a unique username. By default, the password is same for all users and the user can change the password individually. The SQL Server stores its password as a hash in the Master.MDF file.
Different version of SQL server:
- SQL Server 2005 code named “Yukon”
- SQL Server 200 8 code named “Katmai
- SQL Server 2008 R2 code named “Kilimanjaro”
- SQL Server 2011 code named “Denali”
- SQL Server 2014
- SQL Server 2016
If the user forgets the SQL password, then the user can ask the SQL SA (system administrator). It is because the SQL SA can easily reset the user’s password. But, what need to do if the administrator forgot the password?
First, we know the methods through which the user’s password changes. You can apply any of the below manual procedures:
Method 1: Using SQL Server Agent Service
Step 1: First, click Start and then go to All Programs -> Microsoft SQL Server (Your SQL version) -> Configuration Tools -> SQL Server Configuration Manager.
Step 2: Click on SQL Server Services, in the SQL Server Configuration Manager.
Step 3: In the details pane, right-click on the SQL Server Agent (<instancename>) -> Properties.
Step 4: A SQL Server Agent Properties dialog box open and here, on the Log On tab, type the Password and Confirm Password and then click on OK button.
Method 2: Using SQL Server Configuration Manager
Step 1: Click Start, then All Programs -> Microsoft SQL Server (your SQL version) -> Configuration Tools.
Step 2: Now, click on SQL Server Configuration Manager. Here, click on SQL Server Services.
Step 3: Right click on SQL Server (<instancename>) -> Properties in the details area. Here, on the Log On tab, enter a Password and Confirm Password, and then click on OK.
Note: On an SQL Server, stand-alone instance the password change immediately, without restarting SQL Server. And, on an SQL Server clustered instance, you might take the SQL Server resource offline, and need to restart it.
Above, we’ve discussed about the manual procedure to reset the user’s password. Now, switch to the Master.MDF file. The Master.MDF is managed by SQL SA and this file stores all the information about users. If you lost your SA password then there are many ways to recover the password manually.
Note: To prevent from blocking the MASTER.MDF, stop the SQL Server service.
Method 1: Using SSMS management console
Step 1: Open SSMS management console. Check under Start -> All Programs -> Microsoft SQL Server (write version here without bracket) -> SQL Server Management Studio Express.
Step 2: Login into SQL Server using Windows Authentication.
Step 3: In Object Explorer, under Logins folder open Security folder. Right click on SA account and go to Properties.
Step 4: Now, enter desired password in front of “Password” and “Confirm Password” field and click OK.
Method 2: Using SQL Script
Step 1: Open the SQL Server Management Studio.
Step 2: Then, open a New Query and copy, paste, and execute the following:
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
Note: Here, NewPassword is the password you would like to use in SA account.
Method 3: Using command prompt
Step 1: Go to the command prompt of the server and osql –L.
Step 2: Then, copy full name of SQL Server and type: OSQL -S <insert_servername_here> -E.
Step 3: Execute the following query: sp_password NULL, ‘<insert_new_password_here>’, ’sa’.
Step 4: Then, GO.
Method 4: Use this method, when you have SA disabled
Step 1: Start SQL server as single user using -m flag via command line.
Step 2: Run as Administrator. On the Server Roles page, select sysadmin and reset password.
After following, all the above methods, if you’re still unable to login to the SA account. Then, you have one option through which you can easily and effectively recover your SA password. Use third party SQL Server Password Recovery Tool. This tool has many unique features including auto detect, preview of MS SQL server users, crack password for individual as well as SA, etc. Watch the full demo of SQL Server Password Recovery Tool.