Efficient ways to reset lost sql sa password

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Efficient ways to reset lost sql sa password

Author

Topic

yitiana
Starting Member

7 Posts

Posted - 2010-09-19 : 07:42:52

Have you ever forgot or lost your SQL Server sa password? Did you hava any ideals to reset your lost sa password? Or choosed to reinstall MS SQL Server on your PC again? I once forgot my SQL sa password and I reinstalled SQL Server at last, because I failed to find some good methods to solve the problem. What annoying and terrible experience it is! In order to avoid reinstalling SQL Server for a second time, i collect some methods and try them by myself, to reset SQL Server Password for sa account.

Method 1: Reset SQL Server sa password by Windows Authentication

If Builtin/Administrator is present in SQL Server, you can login with an ID which is member of Administrators group and reset sa password in SQL Server. Just do as follows:

Step 1. Login into SQL server using Windows Authentication.
Step 2. In Object Explorer, open Security folder, open Logins folder. Right Click on sa account and go to Properties.
Step 3. Type a new SQL sa password, and confirm it. Click OK to finish.

This way is easilily to reset the sql server sa or other account password, but you need to have the valid Builtin/Administrator account.

yitiana
Starting Member

7 Posts

Posted - 2010-09-20 : 00:10:44

And here is another way that maybe also could reset ours lost sa password, but i have problems to verify this way, can somebody give some ideas about the method.

Method 2: Use the Query Windows in Management Studio to reset sql sa password

Step 1. Open SQL Server Management Studio
Step 2. Open a new query
Step 3. Type the follow commands and excute:

[CODE]GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO[/CODE]
The NewPassword is set for your SQL Server sa account.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-20 : 07:57:06

quote:
This way is easilily to reset the sql server sa or other account password, but you need to have the valid Builtin/Administrator account.

That's not entirely true, you only need another login that has sysadmin privileges on that SQL Server. In fact it is a recommended security practice to remove BUILTIN\Administrators from your SQL Server to prevent unauthorized access.

yitiana
Starting Member

7 Posts

Posted - 2010-09-20 : 22:46:09

Use command prompt is also an easy way to reset lost sa password. See the commands below:

Method 3: Use Command Prompt to reset your lost SQL sa password

      Osql -S yourservername -E
      1> EXEC sp_password NULL, 'yourpassword', 'sa'
      2> GO

Of course, you can reset your other sql account password, just by changing the 'sa' accout to your another sql account.

yitiana
Starting Member

7 Posts

Posted - 2010-09-22 : 05:00:21

spam removed again, account locked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources