Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-06
5
Medium Priority
?
2,744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 8

Accepted Solution

by:
pvlier earned 668 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 668 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 664 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 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