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
Solved

Change SQL Server 2005 password for SA

Posted on 2009-07-06
9
377 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
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.

 

Author Comment

by:rayluvs
ID: 24807437
Hi,

Non worked.  Can u please describe the DAC option?

Thanx!
0
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 Integration Service connecting to 2012 3 31
First Max value 3 27
SQL R 21 25
Where is the Help Section? 8 24
In this article I will describe the Copy Database Wizard 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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

840 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