snippetsqlMajor
How to add new sysadmin account when no sysadmin accounts exist
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
I was set up for just Windows Authentication so I hacked the
How do I enable the
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:
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.
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.exeThis 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.