Efficient ways to reset lost sql sa password

Question

Efficient ways to reset lost sql sa password

yitiana

SSC-Addicted

Points: 478
More actions
September 19, 2010 at 6:30 am

#148700

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.

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

another way is to connect via the Dedicated Administrative Connection (DAC)

a perfect example would be you removed builtin\administrators and then forgot the sa password...which was the only sysadmin on your instance.

The security assumption is a local administrator to the server should not be locked out of the services on that machine, so by connecting with Windows Authentication to the DAC gets the local administrator in so he can do what you did...reset the password, add administrators, whatever via GUI or script.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Oliiii SSCertifiable Points: 5328 More actions · Answer 2

You won't be able to connect using DAC if your sysadmin account are either locked out or removed, if you still have a nt user as sysadmin, just connect using a regular connection (no need for DAC).

You should always keep a nt user as sysadmin so you can reset it's password by other means, unless of course it's a DMZ server or if a reinstall is better than letting a nt user sysadmin.

SQLCharger SSCommitted Points: 1958 More actions · Answer 3

Also the SQL service account has to be sysadmin 😉

Cheers,

JohnA

MCM: SQL2008

yitiana SSC-Addicted Points: 478 More actions · Answer 4

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:

GO

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]

GO

USE [master]

GO

ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE

GO

The NewPassword is set for your SQL Server sa account.

Oliiii SSCertifiable Points: 5328 More actions · Answer 5

The first line is not required (at least it's not the reason you forgot your password 🙂 )

You can also get rid of the MUST_CHANGE, since you'll be the one using it you might as well set it right this time.

And if for some reason the login is locked you can add UNLOCK at the end 😀

yitiana SSC-Addicted Points: 478 More actions · Answer 6

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' to your another sql account name.

yitiana SSC-Addicted Points: 478 More actions · Answer 7

Emm, if there still have trouble on resetting your sql sa password, you can try to get help from some sql server password recovery software, which can easily and instantly reset lost sql sa password.

rokuba SSC Enthusiast Points: 164 More actions · Answer 8

rokuba

SSC Enthusiast

Points: 164

October 23, 2010 at 11:57 pm

#1241320

Here is the solution

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

yitiana (9/19/2010)
Have you ever forgot or lost your SQL Server sa password?

I'm always amazed at how many people do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wayne.mcdaniel SSCommitted Points: 1546 More actions · Answer 10

Jeff Moden (10/24/2010)
yitiana (9/19/2010)
Have you ever forgot or lost your SQL Server sa password?
I'm always amazed at how many people do such a thing.

Probably more likely that the one person who knew quit/died/got fired/etc...

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

How to Connect to a SQL 2005 Server When You Are Completely Locked Out

http://www.sqlservercentral.com/articles/Administration/68271/

http://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx

Fox87 Default port Points: 1424 More actions · Answer 12

Hi Guys,

The script worked on a instance where I had Sysadmin rights to the DB.

But there is another server that was installed by the previous DBA with no documentation and no one knows the sa password. I don't have sysadmin rights to the sql server. The local windows administrator account don't have sysadmin the the SQL Server as well.

Now i have followed the steps on this article "http://www.sqlservercentral.com/articles/Administration/68271/" but did not succeed because I can't create a login for the builtin\administrators from Windows.

Any other methods/ideas on how to change the sa password without any software?

rokuba SSC Enthusiast Points: 164 More actions · Answer 13

rokuba

SSC Enthusiast

Points: 164

November 5, 2010 at 4:45 am

#1247040

attempted?

Fox87 Default port Points: 1424 More actions · Answer 14

Thanks rokuba,

Enabled the username in AD who installed the instance and connected with the Windows Auth and found that the user was a sysadmin member 🙂 Now I have sysadmin and I changed the sa password without a problem 🙂