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
Microsoft SQL Server 2008Statistical Analysis System (SAS)

Avatar of undefined
Last Comment
garychu

8/22/2022 - Mon
Peter Chan

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 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.
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Peter Chan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.