Restoring administrative rights on a SQL installation

Background

By default, SQL 2008R2 and later deployments only add the installation account to the sysadmin role for a SQL instance. If that account is deleted, disabled or otherwise disposed of, then there will be no way to administer that particular SQL installation even if you use a Domain Admin account.

It is good practice, therefore, to specify a local (not domain) group as SQL administrators during the initial installation so you don’t have to do this. For this example, a local group called LocalSQLAdmins will be added. It is not recommended to use the local Administrators group as all Domain Admins are added to this group by default. Let the principle of least privilege be your guide.

Tools

The psexec utility, part of the pstools kit downloadable from Microsoft, is used in this process.

Additional Notes

The process is instance specific and will have to be repeated for each SQL installation on a server.

Process Overview

• Place the SQL instance into single user mode
• Connect to the SQL instance using the Local System account (this is an all powerful local account and is needed as your own account will, obviously, not have sufficient rights. Note that the local System account has no access to the network so make use that you have copied psexec to your SQL server before commencing this process).
• Use command line SQL to create the necessary SQL logins and assign the correct roles.
• Place the SQL instance back into multi-user mode.

Steps

1. Start SQL Configuration manager
2. Put the instance that you need access to into single user mode. Right click on the instance under SQL Server Services, select Properties and then the Startup Parameters tab. Now Add -mSQLCMD to the end of the startup parameters
3. Restart the SQL service for the instance
4. Start an elevated command prompt
5. Change to the directory where you have copied psexec
6. Type psexec –s –i cmd (this will spawn a second command prompt running under the local System account)
7. In the second cmd prompt, type whoami to confirm that you have successfully impersonated the Local System account.
8. Connect to the SQL instance by running sqlcmd –S SERVERX\MYINSTANCE (servername\instancename – the default instance on SQL Express instance is \SQLEXPRESS, for example)
9. Then enter the necessary commands at the > prompt
10. Create login [SERVERX\LocalSQLAdmins] from WINDOWS
11. Go
12. (if SQL 2012 or later) ALTER SERVER ROLE sysadmin ADD MEMBER [SERVERX\LocalSQLAdmins] or
    (if SQL 2008/R2) EXEC sp_addsrvrolemember 'SERVERX\LocalSQLAdmins', 'sysadmin';
13. Go

There is no confirmation message if the commands are accepted. If the commands are not accepted an error message will be displayed.

4. Close both command prompts
5. Go into SQL Configuration manager and remove the –mSQLCMD entry from the Startup properties from the instance that you changed
6. Restart the SQL services for that instance.
7. Add your domain account to the LocalSQLAdmins account and then log off.
8. Log back on and open SQL management Studio. Verify that you have supreme powers over the SQL instance.