Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Change SQL Server 2005 password for SA

Posted on 2009-07-06
9
Medium Priority
?
390 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
[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
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

618 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