Solved

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

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now