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
please help me……
I have the same probleme whitch you talked about it in your artical but in SQL server 2008 R2 I can’t change the deafult database i can’t login to instance
please please pleas
This also worked for me to change user for database file
but not the command like ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
Thank you
Fantastic , it worked
Thank You
Thank you…! it worked for me…
Thanks lot.. i was finding for it.. but after login, when i am adding new database it is giving error..
Thank you !!
Also confirm that the account’s default database is set to MULTI_USER mode. I got users complaining about login error 4064 recently after I completed a database maintenance operation and forgot to change it back from RESTRICTED_USER.
Kudos Pinal… thanks!
Thanks Its worked…
Thanks Its worked
Thanks from Nicaragua, its worked.
Muchas gracias, thanks from Nicaragua.
mmmh i dont knw hw i can thank you Dave for this script help
infact had to close the busness for two days coz of lucking the solution,i didnt know that my help was wth you..thanx
Thanks, this solution solve my problem
Solved my problem!! Thx, i’ve been using your solutions for a log time and never expressed my gratitude.
Thank you soo much!!!
Thanks. I was using windows authentication to login, I changed the default db to one I was working. While testing deleted the db and then realized what had happened.
Nice!