Solved

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge Statement 3 39
tempdb latch contention 12 47
How to revise an Access 2003 query into a SQL Server 2008 SQL statement? 1 24
TSQL previous 5 22
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

11 Experts available now in Live!

Get 1:1 Help Now