Ajarn Mark Caldwell Blog

Bringing Business Sense to the IT World…

Regaining SysAdmin Access after a Lockout

Today I had to find a way to regain SysAdmin access to an instance of SQL Server when I technically had no permissions.  Here is how I did that.

Every developer on my team is setup to be able to work 100% stand-alone.  That is, they have everything on their local machine, including a recent copy of the database, to do all their work even if the network goes down, which it used to do on occasion.  For SQL Server development, each has a copy of SQL Server 2008 R2 Developer Edition installed, and it was one of these that I had to regain access to.  Fortunately this did not happen to a production instance, but in a pinch, these techniques would work there, too.  Normally, I am setup with SysAdmin privileges on each of my team’s SQL Server instances so that if anything goes sideways, I can help them recover or pick up work-in-progress.  They are also SysAdmins on their own instances so they have full capability to do anything they might need to in order to get their job done.  But recently, one of my team members left the company, and as I was reviewing his machine to determine whether it needed a complete rebuild before the next developer could use it, or just a little cleanup, I discovered that I no longer had any access to the SQL instance.  And because this was SQL 2008, we had, as a matter of normal installation technique, left out the Built-In/Administrators group, so even though I was also a member of the local Administrators group, that did not get me anywhere by default.

Somewhere in the back of my mind I had a faint recollection of having heard or read about a technique where you could regain full control of a server even if you didn’t have normal access to it.  I was thinking that maybe it was related to the Dedicated Administrator Connection, but couldn’t remember and was pressed for time so I was not inclined to spend a bunch of time searching online.  So, I went for a very handy shortcut…the #sqlhelp hashtag on Twitter.  I quickly described my scenario and within just a few minutes I had multiple responses back with instructions about how to do it and links to this blog entry by Raul Garcia and this one by Argenis Fernandez that describe two different approaches to getting yourself back in the SysAdmin role.  Since this was a local developer edition and it is easy to stop and restart, I went with the first approach which involves putting the SQL Server into Single-User mode and then as a member of the local Administrators group, I was able to use SQLCMD to add myself back into the SysAdmin role.  I suggest that you read both articles to be aware of the features and risks associated with them, including the potential security risk that #2 reveals is inherent in the normal, and recommended-by-Microsoft configuration.

And an extra thanks to the awesome SQL Server community that is active on Twitter as well as blogs and forums, and truly helps one another.  You guys really helped me today when I was in a crunch.  You’re the best!

Legacy Comments

Aaron Sentell
re: Regaining SysAdmin Access after a Lockout
Thanks for publishing this. I will certainly be able to put it to use. At least one developer where I work had SQL installed as part of their laptop image and couldn't login to the instance. The BUILTIN\Administrators group had been removed, and the sa password was unknown. Had I known about these back doors I could have taken care of that for them. Maybe I will now!

smart card
re: Regaining SysAdmin Access after a Lockout
There are so many issues that we come across in our work while on our computers and I only wish that we had more tips like these where it is possible to regain SysAdmin access even after a lockout which can happen with absolute no warning!! I found this article fascinating and I am sure will so many others like me!! I have already bookmarked this page for future reference and sent it to all my friends as well!