We help IT Professionals succeed at work.

Reset MSSQL sa password

13,948 Views
Last Modified: 2016-02-13
A server computer comes with Windows SBS 2011 Std pre-installed.
Out of the box, an instance of MSSQL 2008 R2 (Express Edition with advanced services) is also pre-installed to manage SBSMonitoring. Installation is apparently in mixed mode.

I am able to access via Server Management Studio, using a Windows logon (domain-admins).
However, access is very limited. An sa SQL logon seems to be needed.
But I have no way of knowing the sa SQL logon password.

I tried re-setting the sa password (while connected using the Windows logon).
Unsurprisingly, it failed with the following message;

"Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)"

Nor could I add a new logon and give it a sysadmin role.

Given this situation, would an Expert be kind enough to provide some help.
I recall vaguely that this may be achieved via SQLCMD .
But I hesitate to try without some expert guidance.

Thanks
Comment
Watch Question

Peter ChanProblem resolver

Commented:
Try this:

Reset SA password using SSMS
1Open SSMS management console. This is usually under Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio Express.
2Connect to your instance of SQL Server via Windows Authentication.
3Once open, navigate to “Logins” object under the “Security”. Once expanded, right-click on the ‘SA’ user, and choose Properties.
4Now, enter desired password in front of “Password” and “Confirm Password” field and click OK.
Kent DyerApplication Systems Administrator
CERTIFIED EXPERT

Commented:
DANGER WILL ROBINSON!  Be very careful here..  Even though this maybe perceived as a "low risk" operation, there are a potential of very bad things that can come out of this!  You have been warned, especially with a vendor or proprietary database.

Author

Commented:
Thanks, HuaMinChen.
That's exactly what I have attempted to do.
The result was the failure message below;
"Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)"
Kent: Is it your advice that i should stay well away from attempting anything with this database? I had in mind to compact the SBSMonitoring log which is growing uncontrollably.
Problem resolver
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks again, HuaMinChen.
I checked and found that I was mistaken about the authentication mode used (in the pre-installation).
The mode is actually Windows Authentication only.
Consequently, the sa account was disabled by default.
I guess my question is now answered.
The prompt responses by Experts is very much appreciated.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.