In this article, I will guide you through the steps needed to recover your sysadmin access. Let's picture a scenario where your company's SQL Servers have been recently reviewed by external auditors. As part of this review, it was found that on some of your company's SQL Servers, the BUILTIN\Administrators group and logins other than DBAs are members of the sysadmin fixed server role. The auditors suggested following Microsoft's security best practices by removing the BUILTIN\Administrators and logins other than DBAs from the sysadmin fixed server role.
While applying this security best practice on one of your SQL Server, you inadvertently removed your company's DBA group from the sysadmin fixed server role. To recover your sysadmin privileges, you chose to use the SA account, but you discover that SA was disabled on this SQL Server. At this point, you could rebuild the master database or reinstall the SQL Server and attach all the user databases. However, this could take a while, 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 method that allows you to gain sysadmin access to your SQL Server. SQL Server 2005 and SQL Server 2008 offer a superior disaster recovery option by letting 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, this means that 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 offers proper controls for the file access and other privileges. To use the Configuration Manager tool to recover your system, follow these 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: Ensure there's no space between ";" and "-m". The SQL Server ERRORLOG file will include an entry that says "SQL Server started in single-user mode."
5. Once the SQL Server instance starts in single-user mode, the Windows Administrator account can 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. After the sysadmin access is 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, follow these 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 navigate 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 into 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 demonstrates the process needed to regain your sysadmin access. This procedure should only be used for disaster recovery when no other method to access the system with privileged access (i.e., sysadmin or equivalent) is available.