SQL Server passwords are also crucial for administrators. If the password is forgotten, then the login will take time as you need to reset it. The blog will give you the required solutions to reset the password for your SQL Server.
Ways to Reset the Password of an SQL Server Account
- If ‘Windows Authentication’ is enabled
To reset password using Windows Authentication, you need to have SQL Server Management Studio installed on your system. Install SSMS, if you don’t have. Once done, follow these steps to reset your SQL Server account password.
- Start SQL Server Management Studio. Then, in Authentication, select Windows Authentication option from the drop-down list.
- Click the Connect option. It will lead you to the server dashboard.
- On the left side of the window, double-click on your server account name folder and then navigate to Security>Logins.
- Then from the expanded Logins options, right-click on sa. Then click on Properties option from the list.
- The System Administrator Properties window will get opened. Here, you can reset the password for the SQL account with the given options. After adding the new password, click OK.
- Hence, the password is reset for your SQL Server account.
Reset Password Using SQL Script
There is another option to reset the SQL Server password with Windows Server authentication by running SQL scripts in SQL Server Management Studio. Follow these steps:
- First, open the SQL Server Management Studio.
- Then, open a new query.
- Run the following script syntax as the query to reset password to the SQL Server account directly.
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
Here, enter the password which you wish to set at the place of NewPassword in the script. This password would always be case-sensitive.
- Now, try to login to SQL with these credentials, and if it opens, then the password is reset.
Note: It will create a temporary password using which you can log in to your SQL Server account, and then you are required to reset the password.
- If ‘Windows Authentication’ is not enabled
If your SQL Server does not have Windows Authentication enabled already and requires log in details to open the SQL Server account, you can utilize Command Prompt to set the new password for the SQL Server account. Let us know the steps to do it.
- Type Command Prompt in the search box at the Start Menu of your system.
- Right-click on the application icon and choose the Run as administrator option. Click Yes to confirm. It will run the Command Prompt application with administrator rights.
- In the Command Prompt screen, type osql -L and press Enter.
- Next, type OSQL -S server -E and press Enter. (type your server name in place of server in the command).
- Now, to create a new password for the SQL Server account, type EXEC sp_password NULL, ‘password’, ’sa‘ and press Enter. (Here, type a new password you want to set for your account in place of the password in the command.
- Execute this final command by typing GO and then press Enter.
- Get exit from the Command Prompt application by typing exit and press Enter.
- Try to log in with the newly set password. If it works, the password is successfully set.
So, by using these solutions as per your scenario, you can reset the desired password for your SQL Server account. However, if you face multiple SQL Server errors or database corruption, try Kernel for SQL Database Recovery tool. It is a comprehensive SQL recovery solution to address all sorts of SQL issues, including MDF file corruption. It supports SQL Server 2019 and all previous versions.