Take the 2-minute tour ×
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.

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.

    
I'm not sure there's a proper way to do this. Easiest may be to shut down SQL Server, copy the user database files somewhere safe, uninstall, reinstall, copy the files back and attach them. (Not sure the copying out/back is required, but just to be safe...). And be more careful next time :-| –  Damien_The_Unbeliever May 7 '11 at 15:07
    
I will definitely be more careful next time - however, I'm supprised that SQL server allows you to disable the "last sysadmin". This doesn't seem right to me. It is effectively allowing you to lock yourself out. –  Mark D Jackson May 7 '11 at 15:12
    
It's unfortunate, but no system can safeguard you 100%. Consider if there was a single sysadmin (SQL Login), and whoever knew the password for that account disappeared. You'd be in an equally bad situation. And this way the code doesn't have to be littered with special cases. –  Damien_The_Unbeliever May 7 '11 at 15:25
    
Yes, I can see that scenario is possible too. However, even a warning would suffice to stop you deleting the last sysadmin by accident. –  Mark D Jackson May 7 '11 at 15:52
    
I've done this before as well on my laptop! I just ended up reinstalling. –  Martin Smith May 7 '11 at 16:47

4 Answers 4

up vote 7 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

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
2  
confirmed this answer using SQL 2008 R2. Worked like a charm. As mentioned above, make sure to disable UAC, or open cmd.exe with "run as administrator". –  mateuscb Mar 30 '12 at 13:22
2  
This should be the accepted answer. –  Josh M. Jan 29 '13 at 14:24
    
This doesn't work on SQL Server 2012 R2. Logging as NT Authority\System with public (and not sysadmin) role –  Fulproof Nov 18 '13 at 5:35
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

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
    
I know that I am commenting on this old thread, but the tip above to stop SQL server, then launch in single-user mode, worked great for me. I had almost given up until I found this tip. I had accidentally locked out the sa account only to discover afterwards that it was the "only" account with the sysadmin role. Our server's original Windows auth. logon with sysadmin rights was created on an old AD domain that didn't even exist anymore, so I was pretty much dead in the water, but using the tip above allowed me to grant myself the sysadmin role to get our SQL server app running again. –  user27963 Sep 8 '13 at 4:28

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.