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?
Best Answer
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)