how to reset SA password

Question

how to reset SA password

will75

Mr or Mrs. 500

Points: 575
More actions
April 30, 2013 at 2:44 pm

#274669

I have couple SQL servers that nobody knows the SA password and nobody has sys_admin right to reset it. There is no BUILTIN either. How could I reset the SA password?

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

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

sql-lover SSCoach Points: 18530 More actions · Answer 1

As far as I know, you need at least, to be Local Admin or part of Windows Admin Group on the machine running SQL.

You need to stop the instance, and start MS-SQL on single user mode. Once you start on single user mode, connect using sqlcmd and add yourself or whoever needs it, to the sysadmin server role, this way:

EXEC sp_addsrvrolemember 'CONTOSO\Joe', 'sysadmin';

GO

Now, all those actions are really intrusive: stop sql, star sql, override permissions, etc. So be sure you have permissions for that. Any attempt to do this without proper authorization or informing your bosses or right department, may cause you serious problems, in some cases, loss your job.

will75 Mr or Mrs. 500 Points: 575 More actions · Answer 2

I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.

sql-lover SSCoach Points: 18530 More actions · Answer 3

will75 (4/30/2013)
I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.

If you are part of Windows Admin Group on that machine, and you start MS-SQL on single user mode, you should be able to reset sa:

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005:

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Now, above process is for SQL2005, but I do believe still applies for SQL2008.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You have followed the information in the following article: http://msdn.microsoft.com/en-us/library/ms188236(v=sql.100).aspx?

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 5

There are some password reset utilities out there - Advanced Password Recovery - which can reset things. Not sure the costs - but worth a look as a last resort.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 6

You always can login using windows authentication and reset the password in SQL management studio. if your SQL installs are installed in mixed mode with both SQL and Windows authentication.

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Lowell SSC Guru Points: 323518 More actions · Answer 7

some links i have saved:

adding a sysadmin back toa server:

http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-if-you-do-not-know-the-login-and-passwor.aspx

http://sev17.com/2011/10/22/gaining-sql-server-sysadmin-access/

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!

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 8

If you already in administrator group then just login using windows authentication, Create new user with admin rights and login through that login.

Using new login u also can chaneg password of already existing SA user.

Flloriya SSC Veteran Points: 256 More actions · Answer 9

Ask you SA (system administrator) to reset the password.

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 10

i found this bat file from microsoft which is pretty much bullet proof:

it adds the current user as an administrator to the sql server that is prompted in the command line text.

this was a million times easier than getting the isntance in single user mode and all the other stuff involved in the original lik i provided.

http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=addselftosqlsysadmin&DownloadId=9198

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!

Flloriya SSC Veteran Points: 256 More actions · Answer 11

An SA password of MS SQL Server can be reset by using Windows Authentication. If Builtin/Administrator is present in the SQL Server, you can login with an ID which is member of Administrators group and reset SA password in SQL Server. Just follow the steps shown below:

Step 1: Log in to SQL server by using Windows Authentication.

Step 2: In Object Explorer, open Security folder > Logins folder.

Step 3: Right click on SA account and go to Properties.

Step 4: Type a new SQL SA password, and confirm it. Click OK to finish.

But if Basic Authentication[/i] is selected during SQL Server installation instead of Windows Authentication, then an SQL user cannot be able to follows the steps shown above. In such situation one should check some third-party SQL Password Recovery tools. I would recommend here SysInfoTools MS SQL Server Password Recovery here to reset a new password for SA login to unlock SQL Server. It allows users to reset password for both logins, individual as well as SA login.

Thanks.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 12

"Basic Authentication"? Do you mean "SQL Server Authentication"?

--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)

betty_bakke SSC Enthusiast Points: 105 More actions · Answer 13

Don't worry, you can still regain your SA password in SQL Server with little bit of technical help and the manual solution which i am going to provide, so you can solve your problem without much confusion.

Here is how you can do it with much ease and I am quite sure that this will be a helpful information for you. Visit this post to know the steps: recover SA password[/url] 🙂

Elliswhite SSCommitted Points: 1783 More actions · Answer 14

Change SQL Server Password Using SQL Script

Try this :-

Open the SQL Server Management Studio.

Open a New Query.

Copy, paste, and execute the following:

GO

ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]

GO

USE [master]

GO

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

GO

where NewPassword is the password you wish to use for the sa account.

SSMS Expert