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.
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 administrator
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.
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.
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
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
Go back to your original command window and stop SQL Server using
CTRL+C and y
Finally, restart the SQL Server services and you should be able to access SQL Server as a sysadmin.