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?
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
Thanks a bunch. You just saved me a big headache!
Comment by Victor — February 7, 2007 @ 2:32 pm
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
What version of SQL are you running? I do not see this magical “options” button.
Comment by scott — March 2, 2007 @ 5:23 pm
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
Nah, im on 2000… Go to know for the future. Thanks man
Comment by scott — March 2, 2007 @ 8:48 pm
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
dbas need to learn to make it simple
Comment by lalala — March 13, 2007 @ 10:26 am
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
sorry. its not working. still i get the error 4064.
Comment by Kannan.P — March 20, 2007 @ 9:31 am
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
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
Thank You
Comment by Mark — April 18, 2007 @ 2:54 am
Thank You
Comment by Alex — April 25, 2007 @ 8:09 am
Thank You. You saved my day
Comment by sanpran — April 28, 2007 @ 2:23 pm
Thanks a lot.
It’s working again.
Comment by Gunther — May 31, 2007 @ 6:51 am
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
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
Thank you!
I knew what the problem was, but not how to fix it!
Comment by Mark — August 23, 2007 @ 10:32 am
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
I nearly gave up and re-installed sql. You saved me!!!
Comment by kwagzz — October 25, 2007 @ 12:50 pm
you save my precious life!
Comment by imperialx — November 13, 2007 @ 9:05 am
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
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
Ha..ha…
Thanks man, it’s works.. before i have stupid solution to reinstall
Comment by herman — January 20, 2008 @ 3:56 am
Thank you. Fast and easy.
Comment by trool — March 3, 2008 @ 2:12 am
Great post!!!
This saved me a lot of time..Thanks
Comment by Don — March 5, 2008 @ 5:14 pm
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
Thanks. Straight forward and simple. You saved me a bunch of time also !
Comment by Tim — March 11, 2008 @ 7:47 am
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
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
[...] 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
AMAZING
MANY MANY THAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAks
Comment by Jawad — April 10, 2008 @ 2:53 pm
u are the KINGGGGGGGGGGGG
i hate microsoftttttttttttttttttt what a silly database server
Comment by Jawad — April 10, 2008 @ 2:54 pm
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
it really worked for me.. thanks a lot…
Comment by baru — May 13, 2008 @ 10:45 am
Legend mate,
Worked a treat
Comment by Tom — May 14, 2008 @ 4:47 am
Excellent!!!!!!!!!!!!!
Comment by Aaron — May 14, 2008 @ 7:25 am
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
Thanks breb02… SQL Server 2000 fix…
RUN–>cmd
osql -E -d master
1>sp_defaultdb ‘MYDOMAINNAME\myloginname’, ‘master’
2>go
Later SQL Server versions:
sqlcmd -E -d master
Comment by shell_l_d — October 26, 2009 @ 12:42 am
Wow! I’m really lucky that your expert solution was found! I’ve been spendng whole morning for this… crab! Why Microsoft never prompt any message to alert user that it requires a default database?? Thank you!!!!!
Comment by Vit — May 21, 2008 @ 4:54 am
Well yuo saved me alot of drama, allowed me in easily and then just ran the ALTER SER command to get everything back in working order. Fooken mint
Comment by Seano — June 12, 2008 @ 3:00 am
Thanks a lot!!!
Comment by MrSiezen — June 12, 2008 @ 5:09 am
Hi, do you have a Solution for MSSQL2000? In MSSQL2000 I didn’t fidn the ‘connection property’ tab. It will be good if you included some print screen
Comment by Alvin — July 8, 2008 @ 2:46 am
breb02, U are God to me!!
Comment by sowmya — July 18, 2008 @ 12:41 pm
Thanks so much, it saved me a lot of time.
Comment by candy — August 6, 2008 @ 9:55 pm
Thanks for the hint
Comment by wayne — August 7, 2008 @ 12:16 pm
Thanks, Gordon for the “Note: [Brackets] required on Domain\User accounts.”
Comment by wayne — August 7, 2008 @ 12:19 pm
I kicked myself like havinf wasted time in that blah blah of sqlcmd thing for half an hour .
And the solution for me was just switching the registration property of the server from sql authentication to windows authentication.
I think I was lucky that the login sa had different default database and the windows authentication had different.
Anyways, you definitely made a clue.
Comment by Fuad Ahmed — August 29, 2008 @ 5:33 am
God bless you!
Comment by Keegan Wade — October 21, 2008 @ 5:53 am
This is just plain pitiful. Thanks a bunch for this advice.
Comment by JacKCrustoe — November 26, 2008 @ 11:52 am
Thanks for the useful posting…
You saved my day…
Comment by Prashanth — December 18, 2008 @ 6:09 am
[...] to post of benharell and comment of Fritz [...]
Pingback by Cannot open user default database, login fails « Álvaro Brange’s Blog. — December 19, 2008 @ 9:54 am
Thank you for the post. I made a post with your suggest and comments of Fritz linked to you post.:)
Comment by abrange — December 19, 2008 @ 10:10 am
Glad to help, thanks for the link!
Comment by Ben — December 19, 2008 @ 10:31 am
[...] to this blog post for more [...]
Pingback by Binary Giant » Blog Archive » Cannot open user default database. Login Failed (Microsoft SQL Server, Error: 4064) — December 30, 2008 @ 3:14 pm
Just wanted to say a big thankyou for this post, i’be been going around the houses with this one, and to be honest thought your resoloution would be another dead end, but no!!! it works, thank you thank you thanks you
Comment by Neil Brennan — February 12, 2009 @ 4:00 am
Thanks buddy. It really saved my time.
Comment by Abdul Qadir — April 5, 2009 @ 11:12 am
Thanks a ton. Saved me a bunch of hours….
Comment by Rajender — April 8, 2009 @ 9:14 am
This works fine for single user If I specified the login. However, I have bunch of users, I need to change default database one go. How shall I do it.
Comment by sql — May 28, 2009 @ 4:37 pm
I still don’t get it — I’m using 2000 and I don’t have an options button (that I can see). help.
Comment by Kathy — July 27, 2009 @ 7:36 pm
I’m not sure how to fix this on 2000, maybe someone will be nice enough to answer? My post was only regarding 2005/2008. Good Luck!
Comment by Ben — July 27, 2009 @ 8:30 pm
it’s in portuguese, but if you need, use Google Translator:
Visual Web Developer 2008 + SQL Server 2008 Express: “Cannot open user default database. Login failed.” [resolvido!]
http://b4it.blogspot.com/2009/09/visual-web-developer-2008-sql-server.html
Comment by Marcos Henke — September 10, 2009 @ 8:58 am
Thank you. It helped me and saved my time
Comment by Madhu — September 21, 2009 @ 1:19 am