What happens when you delete the default database for your login (SQL05)

March 9th, 2009 § 2 comments § permalink

Well you can no longer connect to SQL Management Studio. In my case I had detached the default database for my user, and was now getting the error:

Cannot connect to ..
Additional information:

Cannot open user default database. Login failed.
Login failed for user ‘Laura’. (Microsoft SQL Server, Error: 4064)

Solution: (Vista, XP similar)

  1. Open a command promt (start, type cmd)
  2. Type sqlcmd in the command prompt. You should see an error saying basically that you cannot open the user default datacase (Msg 18456)
  3. Type in: ALTER LOGIN [USERNAME] WITH DEFAULT_DATABASE=master
    GO
  4. This should set your default database to master and you will then be able to open SQL Management Studio.
  5. Alternatively, open SQL Management Studio, and in the connect to server dialog press options, and then type master into the connect to database: textbox.
  6. You will then have to create a new query and execute the following text: sp_defaultdb ‘USERNAME’, master

Where am I?

You are currently viewing the archives for Monday, March 9th, 2009 at laurasaur.