Solved

How to know or reset SQL Server's "SA" Password?

Posted on 2010-09-06
5
2,732 Views
Last Modified: 2012-05-10
Dear EE,

I have tried many different password just to pass using SA account in SQL Server, but still failed.
How to know or reset SA password in SQL Server 2008 R2?

I can't add our BUILTIN\Administrator account be sysadmin, that's why we're trying to login in sa account. We our login using Windows Authentication (as default by our system)

Thank you in advance!
0
Comment
Question by:Stiebel Eltron
5 Comments
 
LVL 8

Accepted Solution

by:
pvlier earned 167 total points
ID: 33612297
As found here: http://www.xram.net/blog/entryid/139/resetting-sql-2008-sa-password
---
Fire up the SQL Server Configuration Manager and double-click on the SQL instance you need to reset.
Click over to the Advanced tab.
In the Startup Parameters, prepend –m; to the list to put SQL server into Single-User mode.  Click OK and then right-click on the SQL Server instance and click Restart.
Open up a command prompt, and type:
OSQL –S localhost –E
The –S parameter tells OSQL what server to connect to, and the –E parameter creates a trusted connection.  Capitalization of the switches does matter.
Inside the SQL terminal, type (substituting your new password for newPassword):
EXEC sp_password NULL, ‘newPassword’, ‘sa’
GO
Once that’s complete, type exit to quit.  Go back to the SQL Configuration Manager, remove the –m; from the Startup Parameters, and restart the SQL Server instance.

Make sure SQL Server and the browser are allowed through the Windows firewall, and try connecting from the other computer.

0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 167 total points
ID: 33612309
You can't find it out once it's set but if you're logged into SQL server you can reset it:

exec sp_password null, null, 'sa'

Lee
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33612313
If anything relies on that account's old password it will break the connection for that application.

Lee
0
 

Assisted Solution

by:bssachdeva
bssachdeva earned 166 total points
ID: 33613940
Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role

This MSDN article from Microsoft gives the steps on how to recover System Admin rights in SQL Server 2005 :
http://msdn.microsoft.com/en-us/library/dd207004.aspx
0
 

Author Comment

by:Stiebel Eltron
ID: 33615770
I got it already. I login as local admin & gotcha! Can add account to sysadmin server role.
Right-click to the database that I want to delete & select delete, it's gone!

But my main problem is still hanging at this moment, in whereas I also open it as another ID, regarding Shared Services Administration failure, with Sharepoint.

However, I know that your advise are helpful, so I would like to distribute the points to the 3 of you.

:-)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question