public class ben:harrell

January 15, 2007

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

Filed under: SQL Server 2005, database, dba, errors — benjamin harrell @ 8:28 pm

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

This error bugged the crap out of me tonight and I saw all kinds of crazy answers usually involving some sort of SQLCMD -blah -blah…i think this maybe the problem with command line happy developers/dbas but to make a long story short the fix is EASY and you will kick yourself for not trying this. 

1.  click the options button if the options are not showing.

2.  click the connection properties tab if it is not active

3.  click on the Connect to database: dropdown

4.  type in the name of a database that still exists (ex. ‘master’)

5. connect!

 I heard all kinds of rants and raves about how atrocious it was that microsoft did this to us and I agree it does suck that SQL Server doesn’t do something about this automagically but what would you suggest it do?

Possibly related posts: (automatically generated)

39 Comments »

  1. Big thanks for this post. Fortunately it came up at the top of Google and I can tell by looking around that you’ve saved me a bunch of time.

    Comment by Chris — January 30, 2007 @ 1:04 pm

  2. Thanks a bunch. You just saved me a big headache!

    Comment by Victor — February 7, 2007 @ 2:32 pm

  3. I got this error when I did a command execute insert statement, I was not sure what you were referring too above with the options butto instructutions. So clicking around (I’m in VS 2005) I right clicked on the filename.mdf file name in the server explorer tab and picked “Close Connection”. Then it worked. Not sure why, but I hoped I contributed a possible solution for someone. Thanks.

    Comment by Greg — February 9, 2007 @ 8:50 am

  4. What version of SQL are you running? I do not see this magical “options” button.

    Comment by scott — March 2, 2007 @ 5:23 pm

  5. I was running SQL 2005 when I had this issue. The options button was in SQL Management Studio in the connection dialog. Hope this helps!

    Comment by Ben Harrell — March 2, 2007 @ 6:02 pm

  6. Nah, im on 2000… Go to know for the future. Thanks man

    Comment by scott — March 2, 2007 @ 8:48 pm

  7. Thanks, I spend 4 hrs. on using command prompt to overcome on this issue and your post is greate issue solved in a minute

    Comment by rama7700 — March 12, 2007 @ 9:53 am

  8. dbas need to learn to make it simple

    Comment by lalala — March 13, 2007 @ 10:26 am

  9. Just had this problem. Google results round a few nasty looking fixes and this. Thankfully I used this first. Saved me a lot of time.

    Comment by Chris Jones — March 15, 2007 @ 5:00 am

  10. sorry. its not working. still i get the error 4064.

    Comment by Kannan.P — March 20, 2007 @ 9:31 am

  11. I was having this problem after inadvertently taking a db offline which happened to be the default db for the only user I could remember the pw for (stupid, I know).

    Anyway, my problem was that once I logged in via mgmt studio, set to master, I couldn’t do anything. I couldn’t bring the db back online, or reset the default db of my user or change passwords of other users or anything else. Nothing.

    The, I found this other great post on this:
    http://blogs.sqlservercentral.com/blogs/brian_kelley/archive/2006/12/11/1230.aspx

    Basically, once you gain access, you can run a query to reset the default database for any user and then you’re home free.

    ALTER LOGIN loginid
    WITH DEFAULT_DATABASE = master
    GO

    Comment by Fritz Laurel — March 24, 2007 @ 5:06 pm

  12. Hi,

    a very useful info but please be more detailed next time ;)

    thanks,
    Svet
    http://devcha.blogspot.com/

    Comment by Svetoslav — March 30, 2007 @ 5:36 am

  13. Thank You

    Comment by Mark — April 18, 2007 @ 2:54 am

  14. Thank You

    Comment by Alex — April 25, 2007 @ 8:09 am

  15. Thank You. You saved my day

    Comment by sanpran — April 28, 2007 @ 2:23 pm

  16. Thanks a lot.
    It’s working again.

    Comment by Gunther — May 31, 2007 @ 6:51 am

  17. WOW… all the bull about ALTER LOGIN crap in all other forums… …. you made my day … great … it works now… after struggling for 4 hrs… thanks a lot.

    Comment by Capton Siluvairajan — July 26, 2007 @ 8:28 pm

  18. For those of you who use their Domain accounts, here is the Alter statement to run once you assign the MASTER DB from the above step.

    ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

    Replace [BUILTIN\Administrators] with your [domain\usr]

    Adios

    Comment by Das — August 7, 2007 @ 9:45 am

  19. Thank you!

    I knew what the problem was, but not how to fix it!

    Comment by Mark — August 23, 2007 @ 10:32 am

  20. Thank you! I experienced this problem by simply detaching the database that was set as the default database for my login.

    SQL Server should display a list of other databases the user has access to if the default database is no longer available.

    I still had to run the ALTER command after gaining access:

    ALTER LOGIN [DOMAIN\LoginID] WITH DEFAULT_DATABASE = master

    Note: [Brackets] required on Domain\User accounts.

    Comment by Gordon Bell — September 19, 2007 @ 4:24 am

  21. I nearly gave up and re-installed sql. You saved me!!!

    Comment by kwagzz — October 25, 2007 @ 12:50 pm

  22. you save my precious life!

    Comment by imperialx — November 13, 2007 @ 9:05 am

  23. Hi there
    I ‘m not sure about where the options button is, but closing and reopening connection (just click on the dbname, under data connection) did the trick.
    I’m not a db expert, I tried to solve with Msft forums, got just a lot of sqlcmd incredifantastic crap.
    It actually was a two clicks story.
    Thanks to all the people who posted here and gave the right hint

    Comment by magius — November 29, 2007 @ 9:53 pm

  24. I am domain admin on the server. I tried setting master under the options. Lets me in but still cannot access any object properties.
    I did the alter login for [BUILTIN\Administrators] and did not work. Tried running the alter command on my domain account but my account is not physically created on the server since I am a member of the Administrators group.

    Any other ideas?
    I have also tried executing the “sp_defaultdb [builtin\Administrators] master” and that did not work either.

    I have a SQL account as sysadmin that is the only way I can get in.

    Comment by Shawn — December 18, 2007 @ 4:56 am

  25. Ha..ha…
    Thanks man, it’s works.. before i have stupid solution to reinstall

    Comment by herman — January 20, 2008 @ 3:56 am

  26. Thank you. Fast and easy.

    Comment by trool — March 3, 2008 @ 2:12 am

  27. Great post!!!
    This saved me a lot of time..Thanks

    Comment by Don — March 5, 2008 @ 5:14 pm

  28. For those who are unable to access any objects after login in with the “master” default user database, just rename the database with the old name it has before.

    Then change the user default database to what you want in the user property.

    Fixed!

    Comment by Teh — March 6, 2008 @ 11:46 am

  29. Thanks. Straight forward and simple. You saved me a bunch of time also !

    Comment by Tim — March 11, 2008 @ 7:47 am

  30. Thanks! I found lots of examples but none worked. Until I tried yours - simple. Again, Thanks!

    Comment by Jonathan — April 4, 2008 @ 8:25 pm

  31. Thanks. Info saved lots of time.
    Selecting Option and typing master, and then, useng alter login statement given by several people worked great! Outstanding Site for future reference.

    Comment by ANDRE — April 8, 2008 @ 10:00 am

  32. [...] If this didn’t fix the problem… try this link: http://benharrell.wordpress.com/2007/01/15/cannot-open-user-default-database-login-failed-login-fail... [...]

    Pingback by Unidev - Software Development » Blog Archive » I cannot login to my SQL Server database anymore! — April 10, 2008 @ 9:38 am

  33. AMAZING
    MANY MANY THAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAks

    Comment by Jawad — April 10, 2008 @ 2:53 pm

  34. u are the KINGGGGGGGGGGGG
    i hate microsoftttttttttttttttttt what a silly database server

    Comment by Jawad — April 10, 2008 @ 2:54 pm

  35. Great and fantastic job.
    I got fix in seconds.
    Thanks a lot buddy I was having whole day same problem and sqlcmd and blah blah.

    Wonderfull Benjamin .

    Comment by Aun — April 15, 2008 @ 2:57 pm

  36. it really worked for me.. thanks a lot…

    Comment by baru — May 13, 2008 @ 10:45 am

  37. Legend mate,
    Worked a treat

    Comment by Tom — May 14, 2008 @ 4:47 am

  38. Excellent!!!!!!!!!!!!!

    Comment by Aaron — May 14, 2008 @ 7:25 am

  39. It didn’t work for me your solution…anyway, I suppose u have another SQL Server version

    The problem for this error seems to be the deletion of a db, witch u previously set some credentials on it.
    My solution:
    RUN–>cmd
    sqlcmd -E -d master (osql -E -d master for SQL 2000)
    1>sp_defaultdb ‘Computer\username’, ‘master’
    2>go

    chears,

    Comment by breb02 — May 15, 2008 @ 4:02 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment

Blog at WordPress.com.