— By Lori Brown @SQLSupahStah
Any login that is a member of the sysadmin role can reset the SA password. But, what if you have a hostile takeover by an application or for some strange reason don’t have any other logins as members of the sysadmin role? Don’t laugh! I have actually run into both of these situations. Most places simply lose or forget what the SA password is and then are in a quandary on how to change it. Here is the way to do it. It does require that an outage be scheduled because you will have to stop and restart SQL. The account that is making this change must be a member of the local administrator Windows group.
Stop both the SQL Server and SQL Agent services. I find it is best to do this from command line. (A couple of notes for the instructions below that involve NET STOP or NET START…. Use mssqlserver for a default instance or mssql$<<instancename>> for a named instance. Use sqlserveragent for a default instance or sqlagent$<<instancename>> for a named instance.)
NET STOP sqlserveragent
NET STOP mssqlserver
Next, start SQL Server in single user mode either from command line or from the Configuration Manager GUI and tell the application that only SQLCMD can connect while it is in single user mode to prevent applications or other users from connecting before you can. If you use Configuration Manager add –mSQLCMD to the SQL Server startup parameters as shown below.
Or, here it is if you do this from a command prompt and use SQLCMD to execute an ALTER LOGIN statement to change the password:
NET START mssqlserver /m SQLCMD
ALTER LOGIN sa WITH PASSWORD = ‘Str0ng9@ssw0rd’
GO
EXEC sp_addsrvrolemember ‘SQLRX\lorib’, ‘sysadmin’
GO
Type in exit when you want to exit SQLCMD.
Next, stop SQL Server again and restart both the SQL Server and SQL Agent services. Make sure if you stopped and started the services from the Configuration Manager that you remove the –mSQLCMD from the SQL Server startup parameters.
NET STOP mssqlserver
NET START mssqlserver
NET START sqlserveragent
Once the services are restarted, you should now be able to connect as sa as well as with a domain account that has sysadmin access to the instance. Now…Please go document your new sa password after this….you don’t want to have this happen again, do you?
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Nice. Works fine.