I have received following question nearly 10 times in last week though emails. Many users have received following error while connecting to the database. This error happens when database is dropped for which is default for some of the database user. When user try to login and their default database is dropped following error shows up.
Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)
The fix for this problem is very simple.
Fix/Workaround/Solution:
First click on Option>> Button of “Connect to Server” Prompt.
Now change the connect to database to any existing database on your server like master or msdb.
Now click on connect and it will successfully log you in. Once you login in the system run following statement for your username and it should fix your problem. Make sure to replace [test] with your username and master with yourdatabase name.
ALTER LOGINÂ [test] WITH DEFAULT_DATABASE = master
GO
Watch SQL in Sixty Seconds video to Resolve Connection Error:
[youtube=http://www.youtube.com/watch?v=1k00z82u4OI]
Reference : Pinal Dave (http://www.SQLAuthority.com)
282 Comments. Leave new
This worked for my login on the master database but I cannot get it to work for the actual database I want the login to refer to.
@Kirk
Ask your SysAdmin to give you access to all other databases. Every login should be mapped to a user in a database, only then a login can enter into that database.
Exception to above comment would be, if you enable Guest A/c in all databases, then you can access all databases with out actually having a user in any of the database, Which ofcourse is very dangerous, Guest A/c should be disabled all the time in all databases ( Except Master and Tempdb ).
~ IM.
Thanks heaps for this, thought i’d stuffed it!
thanks,
it worked :)
Saved my skin there mate. Cheers!
Thanks…
Work!
Thank you, It helped me a lot, made me to recover from panic ………
Thank you Ross, I was about ready to commit seppuku there for a bit.
This helped me! Thanks
Muchas gracias!!!
I have error 4064, according to your instructions its doesn’t solve the problem.
Following error showing while i wanted to open new connection.
‘Failed to open a new connection’
Failed to initialize MSDB database for turning (exit code: -1)(DTAClient)
Please advise.
Thanks
D
Many thanks for the valuable and its really a very good post
I used the same solution, and now I can login. But like many others, I get the same error when I try to actually do anything.
Thank you !!! I stuck with this problem for five days.
This did not work for me, I ran into the issue after detaching my default database. I ended up just creating a new admin user and re-attaching the database in question. Problem solved.
I had same problem but the solution Curvian Vynes mentioned did: Right click on database > Properties > Files > set owner equal to the login of the user.
thanxa
it work’s
i love u
This solution worked great and saved me loads of time. Big Thanks. However all was working fine before so I wonder what changed to cause the problem?
Anyway … Thanks.
Thank you so much! It worked very much.
Thanks a lot.
Fantastic solution !!