Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights).

There are no other sysadmin accounts for the instance except SA.

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 reenable it because I am 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?

share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 13:04

This question came from our site for professional and enthusiast programmers.

up vote 10 down vote accepted

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)

share|improve this answer
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 May 8 '11 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 May 8 '11 at 16:58

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

share|improve this answer
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 May 12 '11 at 6:44

SQL Server 2008 is different than SQL Server 2005, in that local administrators no longer inherit 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 EM, and add your account to sysadmin fixed server role
  • stop sql server, restart (at command line or in SQL Server EM
share|improve this answer

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.

share|improve this answer
1  
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. – Shawn Melton Nov 18 '13 at 6:59

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")
    $sqlConnection.Open()
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand("If Not Exists (Select Top 1 0 From sys.server_principals Where name = '$userName')
Begin
    Create Login [$userName] From Windows
End

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')
Begin
    Exec sp_addsrvrolemember '$userName', 'sysadmin'
End", $sqlConnection)
    $sqlCommand.ExecuteNonQuery() | Out-Null
    $sqlConnection.Close()
    Invoke-TokenManipulation -RevToSelf | Out-Null
}
share|improve this answer

protected by Community Jan 7 at 22:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?