How to add new sysadmin account when no sysadmin accounts exist

Question

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?

Answer 1

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)

Answer 2

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.

Answer 3

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)

Answer 4

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
}

Answer 5

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.

https://docs.microsoft.com/en-nz/archive/blogs/sqlman/tips-tricks-you-have-lost-access-to-sql-server-now-what

---

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)

2. 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.

3. 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

  1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’

  2>go

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

  1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

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

4. 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)

Answer 6

I am login with sqlcmd

CREATE LOGIN Dinesh WITH PASSWORD = 'Dinesh';

GO

---

SP_ADDSRVROLEMEMBER Dinesh,'SYSADMIN'

Answer 7

You can do the following:

1. Create a Windows user account with administrative privileges
2. Login in to Windows with the new administrative account
3. Open the SQL Server Management Studio using Windows Authentication
4. Now you are logged in SQL Server as sysadmin, so you can create new accounts or update the sa login.