Michael J. Swart

A nice story for the holidays.

Not too long ago I made a small error that was going to cause me to lose a number of hours of work and the headache that goes along with it. Okay, if I’m being honest it was more than a small error; it was a huge snafu! And although the only affected systems were my own development box. I still had that sinking feeling in my stomach the moment I realized my situation.

My blunder

On my development server I have SQL Server 2008 installed and I was poking around and playing with some of the more general security features. I wanted to find out what different users could do when granted various privileges. And so as part of this poking around, I granted myself (DOMAIN\MSWART) access to the server and I disabled the BUILTIN\Administrators principal.

Then after more poking, I wanted to remove my Network Service account from the set of logins. Now since the Network Service account uses my computer’s credentials, the login was called (DOMAIN\MSWART$).

You might see where this is going. I forgot the ‘$’ symbol and wound up removing any rights that I had on the machine.

What this meant for me

Luckily this was a development server and any data I cared about was backed up. The real kicker is that I had no way of accessing the database server. To me it looked like this meant going through the process of reinstalling SQL Server which is easier said than done. See Euan Garden’s experience here and Victor Hurdugaci’s experience here.

What I ended up doing instead

But guess what. Management Studio was still open and I hadn’t refreshed Object Explorer so I could still see the list of principals for my server. There was still one login that had access: NT AUTHORITY\LOCAL SERVICE.

Well what else was there to do? I created a windows service. On startup for the service, I used ADO.Net to read from a temporary text file and execute the contents on my database.

I installed the service using InstallUtil.exe I ran a couple scripts a few times (checking and reading any errors in the Event Log). After a few trials I attempted to add DOMAIN\MSWART back as an admin…

And it worked!

I was in! I could do anything I needed to do again. The whole ordeal cost me about twenty minutes (or it saved me three and a half hours if you’re the glass-is-half-full kind of person)

What did I learn?

Don’t do that… for starters. I also found out that if a service can access your database, then so can anyone who has a decent amount of control over the computer.

And from another point of view, I learned how to write quick and dirty windows services.

Update! (Jan 2, 2009)

Joe Sack pointed out (via Jason Massie’s blog here) that I could have more easily gained access by restarting the SQL Server service with the -m or -f options to put SQL Server in single user mode. Details at Microsoft’s troubleshooting page: Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out