Gaining sysadmin access to your SQL Server

The following steps show how to give a windows account sysadmin privileges to SQL Server. This method is typically used when you are unable to access SQL Server as a sysadmin by other means.

Prerequisites:

1. A windows account which can log onto the machine hosting SQL Server and has local administrator privileges.
2. An outage window for shutting down SQL Server.
3. Stop any applications which may want to connect to the SQL Server, if known.

Steps

Stop the SQL Server instance.image01
image02

Note: You may want to also stop SQL Sever Reporting Services, if it is running, as it will want to continually connect to the SQL Server.

Start a command prompt with Run as administratorimage03

Note: I started an elevated permission command prompt by clicking on the easily accessible PowerShell icon and entering cmd

Navigate to folder containing sqlservr for the instance and start SQL Server in single user admin mode e.g. for SQL Server 2012 use:

cd "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn"
 

sqlservr -sMSSQLSERVER -m

 
Note: The -s and -m are lower case. Your path to sqlservr may be different and if you are accessing an instance then use the instance name instead of MSSQLSERVER e.g. for an instance called INST1 you would use -sINST1
 
For some reason if you directly copy and paste the above syntax it doesn’t work and you’ll see the following message in the SQL errorlog. Manually typing the syntax does work.
 
image1
 
image04

You’ll see something similar to the below.

Note: I hadn’t stopped SQL Sever Reporting Services and we can see it attempting to connect to SQL Server.image05

Open another elevated permission command prompt and run

sqlcmd

Note: If you are connecting to a named instance use -S .\instancename with the S being uppercase e.g. for an instance called INST1 you would use:

sqlcmd -S .\INST1
image06

The following syntax shows me adding the local administrator on a server called TEST to the sysadmin role:

CREATE LOGIN [TEST\Administrator] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [TEST\Administrator]
GO
EXIT

For pre SQL Server 2012 you would use:

CREATE LOGIN [TEST\Administrator] FROM WINDOWS
GO
sp_addsrvrolemember [TEST\Administrator],[sysadmin]
GO
EXIT

image07

Go back to your original command window and stop SQL Server using
CTRL+C and y image08

Finally, restart the SQL Server services and you should be able to access SQL Server as a sysadmin. image09

image10

Leave a Reply