How do I grant myself admin access to a local SQL Server instance?

Question

I installed SQL Server 2008 R2 to my local machine. But, I can't create a new database because of rights (or lack of).

"CREATE DATABASE PERMISSION DENIED"

So, I tried to assign the admin privileges to my current login

"User does not have permission to perform this action."

I also tried to create a new login that would have admin privileges but with no luck. How do I grant myself admin rights so that I can create a database? I can re-install, but I prefer not to.

Menagerie

Are you trying to connect via SQL Server authentication or Windows Authentication? – Repairman

Windows authentication (see title). I'm connecting successfully. The errors come from attempting to perform admin tasks. – Menagerie

Answer 1

Yes - it appears you forgot to add yourself to the sysadmin role when installing SQL Server. If you are a local administrator on your machine, this blog post can help you use SQLCMD to get your account into the SQL Server sysadmin group without having to reinstall. It's a bit of a security hole in SQL Server, if you ask me, but it'll help you out in this case.

Tound

It isn't a security hole, local administrators need to have rights to sql server, in fact a local administrator would anyway be able to access sql server's data by copying the data files to another machine (and then copying back if needed), so your only way around this is to limit administrative permissions only to the ones who deserve it.. – Downfall

THAT is supposed to be easier than uninstall / reinstall? Hardly! In my case, we were migrated to another domain; all existing logins worthless. – Circumfluent

Link is dead (and link-only answers are discouraged). https://mcmap.net/q/193386/-how-do-i-grant-myself-admin-access-to-a-local-sql-server-instance has the same instructions as the original blog post – Duckpin

@yoelhalb I will respectfully but firmly disagree with you. Local administrators should not have admin access to SQL Server. – Lennie

@Lennie By definition, a local administrator has full access to everything on the local system. If there's an SQL server running, then that's in scope too. If you have a user that shouldn't have admin access to the SQL server, then they shouldn't be a local administrator and you should set up their access rights and permissions accordingly. – Soulful

Answer 2

Open a command prompt window. If you have a default instance of SQL Server already running, run the following command on the command prompt to stop the SQL Server service:

net stop mssqlserver

Now go to the directory where SQL server is installed. The directory can for instance be one of these:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Figure out your MSSQL directory and CD into it as such:

CD C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Now run the following command to start SQL Server in single user mode. As SQLCMD is being specified, only one SQLCMD connection can be made (from another command prompt window).

sqlservr -m"SQLCMD"

Now, open another command prompt window as the same user as the one that started SQL Server in single user mode above, and in it, run:

sqlcmd

And press enter. Now you can execute SQL statements against the SQL Server instance running in single user mode:

create login [<<DOMAIN\USERNAME>>] from windows;

-- For older versions of SQL Server:
EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';

-- For newer versions of SQL Server:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>];

GO

Source.

UPDATED Do not forget a semicolon after ALTER SERVER ROLE [sysadmin] ADD MEMBER [<<DOMAIN\USERNAME>>]; and do not add extra semicolon after GO or the command never executes.

Repairman

Quick note that sp_addsrvrolemember has been superseded by ALTER SERVER ROLE. e.g. ALTER SERVER ROLE [sysadmin] ADD MEMBER [domain\username] – Armelda

Don't forget to add the GO; at the end – Exhibit

Given that many of us are now running SQL Server without SSMS, I feel this is a much better answer. – Extravert

Thank you for this and don't forget to start the service either using "net start mssqlserver" or SQL Server Configuration Management". – Connoisseur

Thank you for this. To other users: note that if you have a named SQL install, you should use -s when calling sqlservr – Martel

Answer 3

I adopted a SQL 2012 database where I was not a sysadmin but was an administrator on the machine. I used SSMS with "Run as Administrator", added my NT account as a SQL login and set the server role to sysadmin. No problem.

Meson

This solution worked for me, with one additional step required: Had to start sql server in single user mode by adding the "-m" flag. This can be done using the "SQL Server Configuration Manager", right click the server instance and select properties, go to the tab "Startup Parameters" and add -m. – Afebrile

This is awesome. So much simpler than the other answers. – Manaus

Definitely fastest and best of all answers – Dawna

Pretty straightforward and works like a charm. Thanks Vince and maets. – Proudfoot

This is actually the correct answer. One note, to get SQL in single user mode I had to run >net stop MSSQL$SQLEXPRESS and then >net start MSSQL$SQLEXPRESS /m – Nicholson

This answer PLUS the comment by @Afebrile is the correct answer. You've got to add the -m parameter to run single-user mode as admin, then you can do all the things! – Kimes

One other hint: If you aren't able to log in as Single-user, check to see if SQL Agent service is running. That will "jump in" first and block you out. Turn off Sql Agent Service, Start the server in single-user mode, then add yourself as sysadmin. More detail here: learn.microsoft.com/en-us/sql/database-engine/configure-windows/… – Caboose

Looks like this does not work with MSSQLLocalDB instance (LocalDB type of SQL Server). – Craal

Answer 4

Here's a script that claims to be able to fix this.

Get admin rights to your local SQL Server Express with this simple script

Download link to the script

Description

This command script allows you to easily add yourself to the sysadmin role of a local SQL Server instance. You must be a member of the Windows local Administrators group, or have access to the credentials of a user who is. The script supports SQL Server 2005 and later.

The script is most useful if you are a developer trying to use SQL Server 2008 Express that was installed by someone else. In this situation you usually won't have admin rights to the SQL Server 2008 Express instance, since by default only the person installing SQL Server 2008 is granted administrative privileges.

The user who installed SQL Server 2008 Express can use SQL Server Management Studio to grant the necessary privileges to you. But what if SQL Server Management Studio was not installed? Or worse if the installing user is not available anymore?

This script fixes the problem in just a few clicks!

Note: You will need to provide the BAT file with an 'Instance Name' (Probably going to be 'MSSQLSERVER' - but it might not be): you can get the value by first running the following in the "Microsoft SQL Server Management Console":

 SELECT @@servicename

Then copy the result to use when the BAT file prompts for 'SQL instance name'.

  @echo off 
    rem 
    rem **************************************************************************** 
    rem 
    rem    Copyright (c) Microsoft Corporation. All rights reserved. 
    rem    This code is licensed under the Microsoft Public License. 
    rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF 
    rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY 
    rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR 
    rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT. 
    rem 
    rem **************************************************************************** 
    rem 
    rem CMD script to add a user to the SQL Server sysadmin role 
    rem 
    rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS. 
    rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>"). 
    rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role. 
    rem            If provided explicitly, the script is assumed to be running elevated already. 
    rem 
    rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin 
    rem            (the box admin is temporarily added to the sysadmin role with this start option) 
    rem         2) connect to the SQL instance and add the user to the sysadmin role 
    rem         3) restart the SQL service for normal connections 
    rem 
    rem Output: Messages indicating success/failure. 
    rem         Note that if elevation is done by this script, a new command process window is created: the output of this 
    rem         window is not directly accessible to the caller. 
    rem 
    rem 
    setlocal 
    set sqlresult=N/A 
    if .%1 == . (set /P sqlinstance=Enter SQL instance name, or default to SQLEXPRESS: ) else (set sqlinstance=%1) 
    if .%sqlinstance% == . (set sqlinstance=SQLEXPRESS) 
    if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%) 
    if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2) 
    rem remove enclosing quotes 
    for %%i in (%sqllogin%) do set sqllogin=%%~i 
    @echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'. 
    @echo Verify the '%sqlservice%' service exists ... 
    set srvstate=0 
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j 
    if .%srvstate% == .0 goto existerror 
    rem 
    rem elevate if <domain/user> was defaulted 
    rem 
    if NOT .%2 == . goto continue 
    echo new ActiveXObject("Shell.Application").ShellExecute("cmd.exe", "/D /Q /C pushd \""+WScript.Arguments(0)+"\" & \""+WScript.Arguments(1)+"\" %sqlinstance% \""+WScript.Arguments(2)+"\"", "", "runas"); >"%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" 
    call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%" 
    del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" 
    goto :EOF 
    :continue 
    rem 
    rem determine if the SQL service is running 
    rem 
    set srvstarted=0 
    set srvstate=0 
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j 
    if .%srvstate% == .0 goto queryerror 
    rem 
    rem if required, stop the SQL service 
    rem 
    if .%srvstate% == .1 goto startm 
    set srvstarted=1 
    @echo Stop the '%sqlservice%' service ... 
    net stop %sqlservice% 
    if errorlevel 1 goto stoperror 
    :startm 
    rem 
    rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED) 
    rem also use trace flags as follows: 
    rem     3659 - log all errors to errorlog 
    rem     4010 - enable shared memory only (lpc:) 
    rem     4022 - do not start autoprocs 
    rem 
    @echo Start the '%sqlservice%' service in maintenance mode ... 
    sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul 
    if errorlevel 1 goto startmerror 
    :checkstate1 
    set srvstate=0 
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j 
    if .%srvstate% == .0 goto queryerror 
    if .%srvstate% == .1 goto startmerror 
    if NOT .%srvstate% == .4 goto checkstate1 
    rem 
    rem add the specified user to the sysadmin role 
    rem access tempdb to avoid a misleading shutdown error 
    rem 
    @echo Add '%sqllogin%' to the 'sysadmin' role ... 
    for /F "usebackq tokens=1,3" %%i in (`sqlcmd -S np:\\.\pipe\SQLLocal\%sqlinstance% -E -Q "create table #foo (bar int); declare @rc int; execute @rc = sp_addsrvrolemember '$(sqllogin)', 'sysadmin'; print 'RETURN_CODE : '+CAST(@rc as char)"`) do if .%%i == .RETURN_CODE set sqlresult=%%j 
    rem 
    rem stop the SQL service 
    rem 
    @echo Stop the '%sqlservice%' service ... 
    net stop %sqlservice% 
    if errorlevel 1 goto stoperror 
    if .%srvstarted% == .0 goto exit 
    rem 
    rem start the SQL service for normal connections 
    rem 
    net start %sqlservice% 
    if errorlevel 1 goto starterror 
    goto exit 
    rem 
    rem handle unexpected errors 
    rem 
    :existerror 
    sc query %sqlservice% 
    @echo '%sqlservice%' service is invalid 
    goto exit 
    :queryerror 
    @echo 'sc query %sqlservice%' failed 
    goto exit 
    :stoperror 
    @echo 'net stop %sqlservice%' failed 
    goto exit 
    :startmerror 
    @echo 'sc start %sqlservice% -m' failed 
    goto exit 
    :starterror 
    @echo 'net start %sqlservice%' failed 
    goto exit 
    :exit 
    if .%sqlresult% == .0 (@echo '%sqllogin%' was successfully added to the 'sysadmin' role.) else (@echo '%sqllogin%' was NOT added to the 'sysadmin' role: SQL return code is %sqlresult%.) 
    endlocal 
    pause

Hueston

Worked for me. One thing to bear in mind, make sure you have the .js extension mapped to the Microsoft ® Windows Based Script Host otherwise this voodoo won't work (I had mine mapped to notepad.exe). – Bittner

Links are dead - "The Archive Gallery has been retired". – Howlett

@Howlett - added the script – Hueston

if you are facing issue adding yourself as admin using the above script. Try adding your login to the database and then run the script. – Insufferable

Someone added this to GitHub. This script is a real-timesaver. gist.github.com/wadewegner/1677788 – Imponderabilia

Watch for other languages, it only works in english. – Allelomorph

Answer 5

Microsoft has an article about this issue. It goes through it all step by step.

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out

In short it involves starting up the instance of sqlserver with -m like all the other answers suggest. However Microsoft provides slightly more detailed instructions.

From the Start page, start SQL Server Management Studio. On the View menu, select Registered Servers. (If your server is not already registered, right-click Local Server Groups, point to Tasks, and then click Register Local Servers.)

In the Registered Servers area, right-click your server, and then click SQL Server Configuration Manager. This should ask for permission to run as administrator, and then open the Configuration Manager program.

Close Management Studio.

In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, find your instance of SQL Server. (The default instance of SQL Server includes (MSSQLSERVER) after the computer name. Named instances appear in upper case with the same name that they have in Registered Servers.) Right-click the instance of SQL Server, and then click Properties.

On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)

Note

For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m and then click OK. (That's a semi-colon then a dash then lower case letter m.)

Click OK, and after the message to restart, right-click your server name, and then click Restart.

After SQL Server has restarted your server will be in single-user mode. Make sure that that SQL Server Agent is not running. If started, it will take your only connection.

On the Windows 8 start screen, right-click the icon for Management Studio. At the bottom of the screen, select Run as administrator. (This will pass your administrator credentials to SSMS.)

Note

For earlier versions of Windows, the Run as administrator option appears as a sub-menu.

In some configurations, SSMS will attempt to make several connections. Multiple connections will fail because SQL Server is in single-user mode. You can select one of the following actions to perform. Do one of the following.

a) Connect with Object Explorer using Windows Authentication (which includes your Administrator credentials). Expand Security, expand Logins, and double-click your own login. On the Server Roles page, select sysadmin, and then click OK.

b) Instead of connecting with Object Explorer, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). (You can only connect this way if you did not connect with Object Explorer.) Execute code such as the following to add a new Windows Authentication login that is a member of the sysadmin fixed server role. The following example adds a domain user named CONTOSO\PatK.

CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;   ALTER SERVER ROLE
sysadmin ADD MEMBER [CONTOSO\PatK];   

c) If your SQL Server is running in mixed authentication mode, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Execute code such as the following to create a new SQL Server Authentication login that is a member of the sysadmin fixed server role.

CREATE LOGIN TempLogin WITH PASSWORD = '************';   ALTER
SERVER ROLE sysadmin ADD MEMBER TempLogin;   

Warning:

Replace ************ with a strong password.

d) If your SQL Server is running in mixed authentication mode and you want to reset the password of the sa account, connect with a Query Window using Windows Authentication (which includes your Administrator credentials). Change the password of the sa account with the following syntax.

ALTER LOGIN sa WITH PASSWORD = '************';   Warning

Replace ************ with a strong password.

The following steps now change SQL Server back to multi-user mode. Close SSMS.

In SQL Server Configuration Manager, in the left pane, select SQL Server Services. In the right-pane, right-click the instance of SQL Server, and then click Properties.

On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove.

Note

For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a very small window. Remove the ;-m which you added earlier, and then click OK.

Right-click your server name, and then click Restart.

Now you should be able to connect normally with one of the accounts which is now a member of the sysadmin fixed server role.

Satchel

Answer 6

Its actually enough to add -m to startup parameters on Sql Server Configuration Manager, restart service, go to ssms an add checkbox sysadmin on your account, then remove -m restart again and use as usual.

Database Engine Service Startup Options

-m Starts an instance of SQL Server in single-user mode.

Standardize

Answer 7

I removed on purpose my sysadmin permissions from MS SQL Server 2019 as I was testing a bug. I was able to regain them without reinstall using single-user mode and then logging as admin in SSMS. I am admin of this machine, otherwise it would not be possible.

1. Go to SQL Server 2019 Configuration Manager and stop the service for your SQL instance (node SQL Server Services-> SQL Server (yourinstancename))
2. From the properties (node SQL Server Services-> SQL Server (yourinstancename)-> Properties -> StartUp Parameters) enter -m, click Add and Apply buttons (You should have a row with these two symbols on it only -m in Existing parameters when you have finished)
3. Confirm with OK.
4. Start the stopped previously service.
5. Start SSMS as admin.
6. Add sysadmin permissions to my user (Security -> Logins -> New login -> Server roles).
7. Remove the additional -m startup parameter you added previously.
8. Restart the SQL Server service.

Reference:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver16

Phi

Answer 8

The other answers are lacking in visual screenshots, this is a concisely summarised version of Microsoft's docs,

1. Open Start Menu, right-click SQL Server Configuration Manager icon and choose Run as administrator.

2. Right-click the instance of SQL Server, and then select Properties.

3. Here we will setup SQL server to start in single-user mode.

On the Startup Parameters tab, add either,

a) Runs SQL server in single-user mode

-m

b) Runs SQL server in single-user mode + Allow unlimited connections through SSMS.

-m"Microsoft SQL Server Management Studio - Query"

I highly recommend (b). Without this, you can't even open Object Explorer and open a new query! Because that counts as 2 connections, and isn't allowed as we're running in single-user mode.

By appending the application name after the -m option, we allow unlimited connections through that application.

For some earlier versions of SQL Server there is no Startup Parameters tab. In that case, on the Advanced tab, double-click Startup Parameters. The parameters open up in a small window. Be careful not to change any of the existing parameters. At the very end, add a new parameter ;-m and then select OK. (That's a semi-colon then a dash then lower case letter m.)

4. Right-click your server name, and select Restart.

5. After SQL Server has restarted, your server will be in single-user mode. Make sure that SQL Server Agent isn't running. If started, it will take your only connection. Also disable SQL reporting services, analysis services, SSIS and everything else except for SQL server engine.

6. From the Windows Start menu, right-click Management Studio and select Run as administrator. This will pass your administrator credentials to SSMS.

a) Connect with Object Explorer. Expand Security, expand Logins, and right click your own login. On the Server Roles page, select sysadmin.

7. Close Management Studio.

These next few steps change SQL Server back to multi-user mode. We need to undo step (3) to (5).

9. In SQL Server Configuration Manager, in the right-pane, right-click the instance of SQL Server, and then select Properties.

10. On the Startup Parameters tab, in the Existing parameters box, select -m and then select Remove.

11. Restart SQL server services and reenable SQL server agent, Reporting Services etc. as required.

References,

Microsoft instructions

mssqltips - graphical tutorial

January

Answer 9

If you're looking to add a domain user, this script will save you some time.

--
-- 1) CREATE LOGIN domain\USERNAME
--
DECLARE @domain VARCHAR(MAX);  
DECLARE @template VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @domain = DEFAULT_DOMAIN();
SET @template = 'CREATE LOGIN "{domain}\USERNAME" FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
SET @sql = REPLACE(@template, '{domain}', @domain)

EXECUTE (@sql)

--
-- 2) GRANT SYSADMIN server level permission on domain\USERNAME 
--
DECLARE @domain VARCHAR(MAX);  
DECLARE @template VARCHAR(MAX)
DECLARE @sql VARCHAR(MAX)

SET @domain = DEFAULT_DOMAIN();
SET @template = 'EXEC sp_addsrvrolemember @loginame = N''{domain}\USERNAME '', @rolename = N''sysadmin'''
SET @sql = REPLACE(@template, '{domain}', @domain)

EXECUTE (@sql)

Simply replace the "USERNAME" with your own username. You don't have to enter the domain, as it will dynamically figure that out using DEFAULT_DOMAIN(). Fun fact: you can also use this to quickly add the same user, over multiple databases on different domains. Run this command using SSMS's "run query on multiple databases" feature.

January