I've been thinking lately to do some sort of blog post series. Every time I do something "stupid", which happens from time to time, I'll do a little blog post on what happened and how I solved it. The reason for this is twofold: I'll have a solution online I can consult if it happens again and other people can benefit from my mistakes as well. Because remember the ancient Chinese proverb: "It's only stupid if you don't turn it into a learning experience". Okay, I might have made that last one up...

The problem

My first Stupid-Me (patent pending) is how I recently locked myself out of SQL Server. What happened? I installed a SQL Server instance on my local development machine so I could test out some small scripts. Typically stuff I'm researching or when I'm writing queries to answer some questions on sqlservercentral.com. In this instance I have a database called Test (what's in a name?) in which I execute most of my queries. To save myself the trouble of changing the database scope to the Test database every time I log into the server and open a query window, I made this database my default database for my domain login. Everything fine so far.

After a couple of months, the database was getting cluttered with all kinds of test tables, stored procedures, views et cetera. Time to clean ship I thought and I dropped the Test database, with the idea of just creating a new one. First I got this lovely message:

I thought one of my query windows was still open with an active connection to the database, so being the smarty pants that I am I ticked the checkbox “Close existing connections” without giving it too much thought. The drop database succeeded, but when I tried to create a new database, I got this beauty on my screen:

Whoops. I dropped my default database. Logging out and in didn’t work:

I couldn't get back in my own SQL Server instance!

The solution

So what did I do? Luckily I had configured the SQL instance to use mixed authentication mode and I had defined an SA account during the set-up. I simply logged in using the SA account, changed the default database of my domain login and behold, I could log in again. An alternative would be to create a new database with the same name as your too soon departed default database.

Creating an SA account – with a secure password of course – is a handy solution to prevent you from locking yourself truly out of SQL Server. A few years back I didn't have an SA account and I did lock myself out because of domain issues. I had to fall back to methods described in this MSDN page: Connect to SQL Server When System Administrators Are Locked Out. Configuring SQL Server to start up using single-user mode and making sure you are the only user is a bit trickier than just using the SA account to log in.

Edit: Be sure to check out the comments below. There are some interesting links to alternative methods.