During testing I deselected the sysadmin rights for my login and now can't re-add it (because I don't have sysadmin rights).

There are no other sysadmin accounts for the instance except the [sa] account.

I was set up for just Windows Authentication so I hacked the LoginMode in the registry to 2 so I could login as sa using SQL Authentication. This does indeed set the login mode to Mixed, however the sa user is disabled by default and I can't re-enable it because I don't have sysadmin rights.

How do I enable the sa login so I can get in and reassign the sysadmin to my normal account? Is there a registry setting for this too or is it stored in the master database?


7 Answers 7


The details presented in this answer are correct - however other answers do provide workarounds that exist to allow SQL Server to be started in a special mode that allows the server administrator to access SQL Server as a member of the sysadmin role.

Even the most uber-admin type of connection option Dedicated Administrator Connection (DAC), which can only be used as a local connection, and lets you undo all kinds of evil, still requires login credentials. So I don't think there's an official way to do this.

The quickest way to resurrect this system may be to shut down SQL Server, copy the user database files somewhere safe, uninstall, reinstall (making sure to service pack up to at least the level you were previously at), copy the files back and attach the databases. (Not sure the copying out/back is required, but just to be safe...).

You'll still need to manually recover server level objects (e.g. logins)

  • 1
    Thanks - I uninstalled the instance and reinstalled. Reattached the database and everything is fine again. A couple of things to watch out for were using the full database engine installer to reinstall the instance (I had a management tools only installer which stumped me for a while). Also you need to change the permissions on the database files so otherwise you get an error when attaching (error 5). Other than that - all worked fine.
    – Mark D Jackson
    Commented May 8, 2011 at 16:51
  • Also, I did make a copy of the database files, but it turned out this was not necessary. The uninstall did not remove the databases. However, I would still recommend copying the database files just in case this behaviour is different on different versions of SQL server.
    – Mark D Jackson
    Commented May 8, 2011 at 16:58

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.

  • 3
    No it does not work in SQL Server 2012 RTM and above. In order to get in you have to restart the SQL Server service in single-user mode as a member of the local admin group of the server.
    – user507
    Commented Nov 18, 2013 at 6:59

SQL Server 2008 and newer do not automatically add the local administrators Windows Group into the sysadmin fixed server role.

When SQL Server 2008 is installed, it prompts you to designate an account to be added to the sysadmin role. If you receive the box after the software is installed, however, this doesn't help you very much.

Fortunately, Microsoft preserved the 2005 functionality when SQL Server is running in single user mode. Here's what you do:

  • log into the server as windows local admin
  • stop sql server
  • at the command prompt, in the directory where sqlservr.exe resides, type sqlservr.exe -m, and press enter - this will start SQL Server in single-user mode
  • open up SQL Server Enterprise Manager, and add your account to sysadmin fixed server role
  • stop sql server, restart (at command line or in SQL Server Enterprise Manager)
  • This solution works beautifully. Just wanted to add that you dont have to be logged in as the local admin to run sqlservr.exe in single user mode, just when you need to log into it thru SSMS or osql or whatever. Commented Nov 1, 2018 at 0:53

This is a great question and someone will be in this situation and needs a rescue out of it ..

All the credit goes to codykonior

If you're a Windows Administrator on the server then you can use a PowerShell v2 script to obtain access with no outage and no known risk. It does this by duplicating the login token of the SQL Server service even if it's running under an AD service account, virtual account, or protected with a per-service SID

Below script will iterate the SQL Server services and will add you to the sysadmin role wherever you're missing :

#GITHUB Link : https://github.com/codykonior/HackSql
$userName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name

$services = Get-Service | Where { ($_.Name -eq 'MSSQLSERVER' -or $_.Name -like 'MSSQL$*') -and $_.Status -eq "Running" }
foreach ($service in $services) {
    if ($service.Name -eq "MSSQLSERVER") {
        $sqlName = ".\"
    } else {
        $sqlName = ".\$($service.Name.Substring(6))"

    Write-Host "Attempting $sqlName"
    $serviceProcess = Get-WmiObject -Class Win32_Service -Filter "Name = '$($service.Name)'"

    Invoke-TokenManipulation -ProcessId $serviceProcess.ProcessID -ImpersonateUser | Out-Null
    $impersonatedUser = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
    Write-Host "Service $($service.Name) on PID $($serviceProcess.ProcessID) will connect to $sqlName as $impersonatedUser"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlName;Trusted_Connection=True")
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand("If Not Exists (Select Top 1 0 From sys.server_principals Where name = '$userName')
    Create Login [$userName] From Windows

If Not Exists (Select Top 1 0 From master.sys.server_principals sp Join master.sys.server_role_members srp On sp.principal_id = srp.member_principal_id Join master.sys.server_principals spr On srp.role_principal_id = spr.principal_id Where sp.name = '$userName' And spr.name = 'sysadmin')
    Exec sp_addsrvrolemember '$userName', 'sysadmin'
End", $sqlConnection)
    $sqlCommand.ExecuteNonQuery() | Out-Null
    Invoke-TokenManipulation -RevToSelf | Out-Null

You can still create a login with 'sysadmin' access. Thanks to Principal Data Engineer at Microsoft (Saleem Hakani) had authored the below on SQL Server Tips & Tricks:

I found this while I was stuck in similar state when I forgot the sa password for my test machine.


Here are the steps you will need to perform:

  1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)

From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.

(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)

  1. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.

    SQLCMD –S <Server_Name\Instance_Name>

You will now be logged in to SQL Server as an Admin.

  1. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.

To create a new login and add that login to SYSADMIN server role:

  1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

  2> go



To add an existing login to SYSADMIN server role, execute the following:


The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.

  1. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

You can do the next:
1) Create a windows user account with administrative privileges
2) Login in windows with the new administrative account
3) Open the Sql Server Mangement Studio using Windows Authentication
4) Now you are logged in SQL Server as sysadmin, so you can create new accounts or update the sa user

  • 2
    Tried this and it didn't work. I created a new admin login account on that machine. SQL server uses the BUILTIN\Users roles (which don't include sysadmin) to log me in. Therefore I still can't make admin type changes.
    – Mark D Jackson
    Commented May 12, 2011 at 6:44

enter image description here

I am login with sqlcmd




enter image description here

  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. Please construct your questions based on the details in minimal reproducible example. Adding table DDL statements, sample DML statements, and providing desired output via a markup table as shown in the example makes it easier for us to provide great answers.
    – Hannah Vernon
    Commented May 14 at 2:56