SQL Server Locked Out - Sysadmin Access

You are newly hired and you found out the previous DBA who manage the database left without informing anyone the sa account password, and there is no way to reach him. SQL login or Windows user accounts with sysadmin privilege were disabled or removed intentionally or accidentally. What to do now?

In order to add a member to sysadmin fixed server role, the login has to be a member of sysadmin. Before we start, here are some basic statements to change password and to grant sysadmin fixed role;

To change sa password.

ALTER LOGIN sa WITH PASSWORD = 'YourNewPassword';

To add a new SQL login and grant it with sysadmin role,

CREATE LOGIN YourLogin WITH PASSWORD = 'YourNewPassword';
ALTER SERVER ROLE sysadmin ADD MEMBER YourLogin;

To add  a Windows login and grant it with sysadmin role,

CREATE LOGIN [YourWindowsLogin] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [YourWindowsLogin];

Before you proceed, please refer to SQL Server Default Sysadmin to get yourself familiar with the default login created by SQL Server with sysadmin fixed role. The steps below utilize these login to connect to SQL Server as sysadmin fixed role, either by changing the password for current sysadmin logins, or created a new login with sysadmin fixed role.

Using BUILTIN\Administrators Group
Prerequisite: BUILTIN\Administrators login exists and is enabled in SQL Server. Member of local administrator group

With SQL Server 2005 installed in Windows prior to Windows Vista and Windows Server 2008, member of local administrator do not need their own SQL Server login, and they connect to SQL Server as sysadmin through SQL Server BUILTIN\Administrator login group. Please note that since SQL Server 2008, this BUILTIN\Administrator login group is no longer automatically granted access to SQL Server. In order for the steps below to work, this group login needs to be manually added in SQL Server.

To add user as local administrator

- Open Computer Management (run compmgmt.msc)
- On the left pane, expand System Tools, expand Local Users and Groups, Select Groups
- Right click Administrators and select Add to Group, and add a Windows user in the pop up windows. Click OK.

If SQL Server 2005 is installed on Windows Vista or Windows Server 2008, or more recent version, member of local administrator group connect to SQL Server with its own login credential (require their own login).  In order to login as BUILTIN\Administrators, local administrator run the client application (e.g. SSMS or SQLCMD) with elevated privilege through 'Run as administrator'.

Connect SQL Server through SSMS and login as BUILTIN\Administrator
- Right click SQL Server Management Studio (SSMS),
- Select Run as administrator

Why it needs to explicitly run as administrator when the account is already member of local administrator, please read further as it is explained below.

Using SQL Server -m or -f startup option
Prerequisite:  Member of local administrator group. If windows user login already exist in SQL Server, the login requires to be enabled.

This may be the most common method. However, this method required to start SQL Server in single user mode, it usually means down time as admin restart the server in single-user mode to address the issue.

When SQL Server service is started with these startup option, any member of server local administrator connects to SQL Server instance is granted with sysadmin fixed role.

-m : start SQL Server instance in single-user mode.
-f : start SQL Server instance with minimal configuration, and in single-user mode.

More information regarding startup option could be found in this msdn article.

To add this option,
- Open SQL Server Configuration Manager
- Select SQL Server services on the left pane.
- Right click the SQL Server instance service, and click properties
- Select Startup Parameters tab on the top. (For SQL Server older than SQL Server 2012, refer note below)
- Type -m or -f in the Specify a startup paramater box and click Add.
- Click Apply and OK.
- Start or restart the service.

Connect SQL Server through SSMS and login with sysadmin fixed role,
- Right click SQL Server Management Studio (SSMS),
- Select Run as administrator

Note: You may need to disable SQL Agent to ensure your connection is the first connection. More detail refer to this msdn article. For SQL Server version older than 2012, click on Advanced tab, double-click Startup Parameters. In the new window, at the very end, add ;-m or ;-f at the very end (you need the semi-colon ; in front of the startup option).

You may wonder why you need to explicitly to 'Run as administrator' when you are member of local administrator. The reason is starting from Windows Vista and Windows Server 2008, Microsoft introduces User Access Control (UAC) feature to improve security by limiting process to run with standard privilege until it is elevated by administrator. Under UAC, local administrator group have two access tokens. A standard user privilege token and administrator privilege token. By default, standard user privilege token is used to perform most action. Refer to this TechNet article on UAC and SQL Server.

Using NT AUTHORITY\SYSTEM
Prerequisite: NT AUTHORITY\SYSTEM login exist and is enabled in SQL Server. Member of local administrator group

In SQL Server 2005 and SQL Server 2008/2008 R2, NT AUTHORITY\SYSTEM are granted with sysadmin role.

An easy way to run an executable as system account is using PsExec. A brief description of PsExec. It is a very light weight program to execute program on remote system and redirect output to local system. It has an option to run as system account, and that is what we are using it for our purpose here.

- Download PsExec into a folder (e.g. C:\PsTool)
- Open a command prompt at that location
- Type psexec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

You will see the user name assigned as NT AUTHORITY\SYSTEM in the SSMS connect to server window.

You can also execute SQLCMD command directly from PsExec.

psexec -s "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" -S YourSQLInstance -E -Q "CREATE LOGIN YourNewLogin WITH PASSWORD = 'YourNewPassword'; ALTER SERVER ROLE sysadmin ADD MEMBER YourNewLogin;"

Note: You may need to modify your SSMS and SQLCMD path accordingly based on your SQL Server version.

Using Per-Service SID
Prerequisite: The per-service SID (e.g. NT Servie\SQLWriter) login used in the step below exist and is enabled in SQL Server. Member of local administrator group

By default, services are running with Per-Service SID account to access resources. As we discussed in previous post, starting from SQL Server 2012,  SQL Server by included these three per-service SID login as sysadmin,

SQL Writer service - NT Service\SQLWriter
SQL Server database engine service - NT Service\MSSQLSERVER or NT Service\MSSQL$InstanceName
SQL Agent service - NT Service\SQLSERVERAGENT or NT Service\MSSQL$SQLSERVERAGENT$InstanceName

Some of the service configurations including the executable file are stored in registry control set. CurrentControlSet subkey is pointing to control set currently use. For example for default SQL Server instance service,

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter

You can find ImagePath entry which specifies the location of executable files for the MSSQLSERVER service.

If we change this entry to point to SQLCMD utility executable located at "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" (For SQL Server 2012), the starting the service will execute the modified ImagePath which is SQLCMD.exe instead. With that understanding, if we change the ImagePath to

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" -S YourSQLInstance -E -Q "CREATE LOGIN YourNewLogin WITH PASSWORD = 'YourNewPassword'; ALTER SERVER ROLE sysadmin ADD MEMBER YourNewLogin;"

Just like as if we are executing SQLCMD command.

By starting the SQLWriter service (e.g. net start SQLWriter), although receive an error it actually execute the command just as it is executing SQLCMD command, and create the login with sysadmin privilege.

Note: You can actually use any of SQL Writer, SQL Server database engine or SQL Agent service for this purpose. As we need to start the service (e.g. net start sqlwriter), which mean we have to stop the service if it is already running. However, since the database engine needs to be running in order for the query to work, of course, I choose SQL Writer service. More details refer to this post from Argenis Fernandez.

As you can see, member of local administrator are used to pretty much change or login with sysadmin fixed role to SQL Server. It could be both desirable and undesirable from DBA perspective.