Link to home
Start Free TrialLog in
Avatar of garychu
garychu

asked on

Reset MSSQL sa password

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
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

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.
Avatar of Kent Dyer
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.
Avatar of garychu
garychu

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of garychu

ASKER

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.