Solved

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

Posted on 2010-09-06
5
2,737 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 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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