Restore access to the SQL Server instance without restarting

Starting with Microsoft SQL Server 2008, by default the local administrators group is no longer added to SQL Server administrators during the installation, this can only be done manually.

As a result, if the administrators group was not added during or after installation, there are situations where administrative access to SQL Server is lost. This may happen when the user from under which the installation was performed is unknown or the password for the sa account is lost, or when the previous database administrator quit and his Windows account, being the only one with administrator rights for SQL Server, was deleted.

Usually in this case, the regular mechanism is used with a restart of SQL Server in single-user mode. However, this approach requires some downtime, and in some systems this is not acceptable.

There is a very painless way to solve this problem without any downtime, provided certain conditions are met. Many have heard and used Mark Rusinovich’s Windows Sysinternals utilities. This kit contains the PsExec utility that allows you to run a process on a local or remote machine. One of the advantages of this utility is the ability to run processes on behalf of an NT AUTHORITY\SYSTEM account, which – unlike “normal” administrator accounts – has access to SQL Server by default.

For this method to work, Windows authentication must be enabled on SQL Server and it must be run and run as an NT AUTHORITY\SYSTEM account, otherwise NT AUTHORITY\SYSTEM account will only have public rights, not sysadmin.

Preparing to restore administrative access to SQL Server

1. Make sure the SQL Server service is running on behalf of your NT account AUTHORITY\SYSTEM.
2. Download and install the current version of SQL Server Management Studio (SSMS) on your SQL server, for example here
3. Download and unpack the archive to SQL server the current version of PsTools (PsExec is included), e.g. PSTools.

Additions to SQL Server Administrator

1. Run the command line with elevated rights on SQL server (Shift + right click, “Run as administrator”) and navigate to the folder where PsExec utility is unpacked.
2. Perform the command below by correcting your actual path to Management Studio:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Parameters:
–i – indicates that you need to start the process interactively
–s – indicates that you need to run the process on behalf of your NT system account AUTHORITY\SYSTEM.

You will get approximately the following (at the first launch a window with the license agreement will appear):

and the SSMS will start.

3. By clicking Connect, you can connect and see the list of objects on the server.

4. Now, go to Security > Logins and add your account as a system administrator, and update the sa password, in general, do everything necessary to make your instance manageable.

This method, although it has a number of limitations, but allows without stopping the database server to restore SQL server management.