Introduction

In this article, I will show you the steps which you require to regain your sysadmin access. Let’s pretend a situation where your company’s SQL Servers had been recently audited by external auditors.  As part of this audit it has been noted that on some of your company’s SQL Servers BUILTIN\Administrators group and logins other than DBAs are members of the sysadmin fixed server role. It has been recommended by the auditors to follow the Microsoft security best practices by removing the BUILTIN\Administrators and logins other than DBAs from the sysadmin fixed server role.

While performing this security best practice on one of your SQL Server you accidently removed your company’s DBA group from sysadmin fixed server role. To regain your sysadmin rights you decided to use SA account but you find out that SA had been disabled on this SQL Server. At this point you could rebuild the master database or reinstall SQL Server and attach all the user databases. However, this could take some time, and it doesn’t guarantee that all your logins, users, permissions, and server configurations will be recovered unless you plan to restore the master database from an old backup. You are now running out of options. What would you do?

There’s a way with which you can gain sysadmin access to your SQL Server. SQL Server 2005 and SQL Server 2008 provide a better disaster recovery option by allowing members of the Local Windows Administrators group to log in with sysadmin access whenever SQL Server is started in single-user mode, also known as "maintenance mode ". However, that would mean your Windows account will need to be a member of the Local Windows Administrators group.

Restarting SQL Server Using SQL Server Configuration Manager

You can use the SQL Server Configuration Manager tool to start SQL Server in single-user mode. This provides proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, use the following steps:

1. Open the Configuration Manager tool.
2. Stop the SQL Server Instance you need to recover
3. Navigate to the "Advanced" tab of the properties of the database engine. In Properties add ";–m" to the end of the list in the startup parameters.
4. Click the "OK" button and restart the SQL Server Instance

NOTE: make sure there is no space between ";" and "-m".The SQL Server ERRORLOG file will include an entry that says "SQL Server started in single-user mode."

5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility and Windows authentication. You can use T-SQL commands such as "sp_addsrvrolemember" to add a login to the sysadmin server role.

EXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin';
GO

6. Once the sysadmin access has been recovered, remove the ";-m" from the startup parameters using the Configuration Manager and restart the SQL Server Instance

Restarting SQL Server using the SQL Command Line:

To use the command prompt to recover your system, use the following steps:

1. Stop SQL Server 2005 using the command, NET STOP MSSQLSERVER
2. Start SQL Server 2005 in Single-User mode using the command:

NET START MSSQLSERVER /m OR SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your path, you’ll need to change to the Binn folder. 

3. Once the SQL Server service has been started in single user mode or with a minimal configuration, you can now use the SQLCMD command from a command prompt. You can connect to SQL Server with SQLCMD and perform the following operations to add yourself back as a sysadmin. 

          SQLCMD –S <Server_Name\Instance_Name>

          This will log you in to SQL Server as a sysadmin .

4. Once you are logged into the SQL Server using SQLCMD, you can add a login to the sysadmin server role with this command. 

    EXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin';
    GO

5. The next step is to stop and start SQL Server services using regular startup options. (remove the –f or –m)

Conclusion:

This article shows you the process which you require to regain your sysadmin access. This process should only be used for disaster recovery when no other method to access the system with a privileged (i.e. sysadmin or equivalent) is available.

References:

• http://msdn.microsoft.com/en-us/library/ms188236.aspx
• http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx