Login failed for user in SQL Server Management Studio

So, you are attempting to connect to the local sqlexpress instance on your machine using Windows Authentication and for some reason it doesn’t recognise you any more.

For example, lets say I have a machine called BLUEBOX and am logged in under my normal local account OPERATOR. In connecting to BLUEBOX\SQLEXPRESS as BLUEBOX\OPERATOR, “Failed to connect to BLUEBOX\SQLEXPRESS”, “Login failed for BLUEBOX\OPERATOR”.

This one had me going for a while, but as I have been recently tidying up my SqlExpress install I have a feeling it had to do with deleting a database, and indeed that turns out to be the case. I had deleted the default database for BLUEBOX\OPERATOR. In my case I logged in as ‘sa’ using SQL Server Authentication and changed the default database for BLUEBOX\OPERATOR to Master. How I would have been able to fix this without logging in as ‘sa’ is beyond me.

So I suppose there are a number of lessons.

  1. Always allow mixed mode access to the local instance or you don’t have that ‘sa’ backdoor when needed.
  2. Always assign your ‘administrative’ login’s default database to Master (or some other database that you aren’t going to delete).
  3. Ask Microsoft why SQL Server doesn’t warn you when you attempt to delete the default database for the current login.

Hope this helps.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s