Solved

Change SQL Server 2005 password for SA

Posted on 2009-07-06
9
372 Views
Last Modified: 2012-05-07
We have misplaced the password for user SA in our SQL2005 with SP2.

However, I have a user that connect successfully via windows authentication.  But can connect via SQL USer/Password.

Is there a way the user with authentication can go in ans change the SA password?

IF not, what woould be the process to change the password.  The SQL2005 is installed in a server 64 bit, 6 gb ram, dual core.

0
Comment
Question by:rayluvs
  • 5
  • 3
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24788897
ALTER LOGIN SA  WITH PASSWORD = '';
0
 
LVL 7

Expert Comment

by:wilje
ID: 24789035
Is the user that can login setup as a sysadmin?  If not, then verify whether or not the BUILTIN\Administrators account has been inactivated or sysadmin has been removed.  If not, then you should be able to add a domain user to the Administrators group on that server, login using that windows users and change the SA password.
If none of that works, then your only option is going to be starting up in single user mode and using DAC to access the server and change the password.
0
 

Author Comment

by:rayluvs
ID: 24789135
ok...we 'll try both...thanx!
0
 

Author Comment

by:rayluvs
ID: 24807437
Hi,

Non worked.  Can u please describe the DAC option?

Thanx!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:wilje
ID: 24807472
What do you mean it did not work?
Is the BUILTIN\Administrators login active in SQL Server?  What were the exact steps that you tried, and how did they not work?  Did you get any error messages?
0
 

Author Comment

by:rayluvs
ID: 24808097
Sorry about that...I meant that the user is setup as a sysadmin and also BUILTIN\Administrators account is inactivated; I didn't have to make any changes.

0
 
LVL 7

Accepted Solution

by:
wilje earned 500 total points
ID: 24808215
Review this article: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx
It oulines how to startup SQL Server in single-user mode and add a user with sysadmin privileges.  Once you have that, you can restart SQL Server normally - login with the user you setup and change the sa password.
0
 

Author Comment

by:rayluvs
ID: 24808324
THANX!!!!!!!!!!!!!!!!!!
0
 

Author Comment

by:rayluvs
ID: 24808327
Worked Excellently !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 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

25 Experts available now in Live!

Get 1:1 Help Now