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!