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
Thank so lots…..^_^
Many thanks. It worked great!!!
Hi,
v have always solved my problems. Thanks a ton
thank u for ur help
Thnx,it solved my problem…many thnx
Thank you so much..
Thanks, the tip was very useful.
Worked great!
Hi
Thanks first for getting me a step forward to start my SQL 2008 server
Now I am able to connect with master but not able to change the default user for the instance as it is offline
When I am trying to bring it on line it is showing the same error 4064 login failed
VERY THANKS MY DEAR FRIEND!
nice. thanks my Database experts was unable to help me. thanks. :)
Working Great thnx..!!
Thanks Pinal Dave.
Error 4064 for connecting to sql server 2005 resolved with the help of your simple process. :-)
Thanks a lot. :-)
Manaa
Thanks Pinal Dave.
It worked :)
Thx champ. It works as described.
I was a bit worried as I had Detached the DB for my login!
nice.
Muchas Gracias Broeshhh.. Pero mi problema tenia algo mas… para aquellos que no les salio quizas es porque hicieron algo antes de que el problema surge por ejemplo en mi caso borre una bd que era usado como database x default sin darme cuenta por eso luego al no reconcoer esta database no me permitia, lo que hcie fue entrar con master como me mencioana qui y luego en vez de hacer el “ALTER” crear la base de datos que tenai antes por medio de Scripts.
ENGLISH: For people who still have the problem, maybe it comes becuase before having the issue, you move or configure something wron, in my case, i delete a database, db which was the default db so the solution was accesing by master and then creating again the old db by scripts….
Options allowed me to connect.
Then ran :
ALTER LOGIN [domain\userGroup] WITH DEFAULT_DATABASE = master
GO
Thanks
Thanks for the help. It worked, OK !!!!!!
sir, i have deleted my default user sqlserver 2008. Now unable to login.
Suggest any way out. Reinstall is not possible.
Thanks it worked.