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
As for me I set everyone to the TEMP database by default. This database always exists and if they run anything in the database by mistake, it disappears on the next reboot. Also, they can’t mess up my master database.
I tried your fix, but I still get the login failure
Thanks for the tip. It worked great!!!!!!
Thanks for this. I locked myself out of my local test account and this fixed it.
In trying to delete a database, and getting “in-use” errors, I found on the web that people were saying to just run
ALTER DATABASE Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Well, I did that, and still couldn’t delete the database, because I was the only one logged into it! (Setting it to a single-user mode doesn’t do anything if you are the one user.)
So when trying to right-click the database and get its Properties, I got the 4064 error about log-in failure (since I was already logged into it, somehow, I guess…). Tried this solution and it will work as long as you then re-log into SQL Server fresh. Then you can right-click the database to pull up its properties and then you can reset the database to “MULTI_USER” at the bottom of the “Options” section, under “Restrict Access”.
Still haven’t figured out how to programmatically drop the database, though.
Thanks,
Tom
Thank you!!!!
hi, thanks for the solution.
its really help to sort out things. accidently detached the database. run into trouble due to unable to login with windows authentication.
thank for help
We have a UserID and login that is used by an application. It has been around since mid-2004. It was working as of last week; today I get the 4064 error message. I can log in as myself, using Windows Authentication, to the database server machine; i can see the program Userid’s default database, and can in fact run SELECT queries against various tables. I ran the ALTER statement that you printed above; I also right-clicked the database and made sure that the program UserID had at least SELECT access to the database (it did). I also set the program UserID’s default database to Master. (The database desired is listed in the program’s connection string.)
When I close and restart SQL Server 2005 and attempt to login as the program UserID, I still get the 4064 error message.
I’m stumped.
Thanks, worked. And much simpler than Microsoft’s KB. Used it in SqlExpress2008
Thanks so much. You were really more than helpful….
Dev
This is the best solution i fount the google.
Thanks
Hey,
i got a problem which i didn’t realize last time. I’m using windows authentication, and my server name is for example “home-pc\server”, So how do i set the default database in this case ?
I have tried using the method you give above and replacing the user name with the server name and i get the message below
[
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login ‘HOME-PC\KEEM’, because it does not exist or you do not have permission.
]
Thanks! The top search hit I tried (MSKB 307864) didn’t work; the SQLCMD syntax generated a “Sqlcmd: ‘-E’: Unknown Option” error. Your solution is more intuitive–and, most importantly, actually works! Thanks again.
i agree… spot on ! chris
So simple !! thanks that worked a treat !
thank u for ur help
I bashed my head against the same rock that Keem did. the solution is to put the qualified user name in brackets like so
ALTER LOGIN [HOME-PC\User] WITH DEFAULT_DATABASE=master
Thx Ross. You were a great help coz I had gotten stuck for all this time on that same position.
Can anyone help me on how to configure my ‘ sql server authentication’ login ?
Thanks Pinal Dave — I had this problem, and am in an environment where I am remote to the box with limited privileges. However, the solution works for me.
Some people have had a problem with their username, and in general I use double quotes for any .NET strings (though the square brackets also work):
alter Login “domainuser” with default_database = master
go
<<>>
alter Login [domainuser] with default_database = master
go
Hi. This did not work for me but this did:
Database properties -> Files
Change owner to relevant user.
This is what I needed – THANKS!
Many thanks….Easy, clean and simple solution!!!!