HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How to add new sysadmin account when no sysadmin accounts exist

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
newaccountsysadminexisthowaccountswhenadd

Problem

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?

Solution

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.

Context

StackExchange Database Administrators Q#11299, answer score: 28

Revisions (0)

No revisions yet.