Tell me more ×
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.
up vote 7 down vote favorite
3

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

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

4 Answers

up vote 5 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
up vote 8 down vote

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
1  
This should be the accepted answer. – Josh M. Jan 29 at 14:24
up vote 4 down vote

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

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.