SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

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:

Reference : Pinal Dave (http://www.SQLAuthority.com)

About these ads

162 thoughts on “SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

  1. 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.

  2. 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

  3. 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

  4. 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.

  5. 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.
    ]

  6. 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.

  7. 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

  8. 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 ?

  9. 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 “domain\user” with default_database = master
    go

    <<>>

    alter Login [domain\user] with default_database = master
    go

  10. Hi. This did not work for me but this did:

    Database properties -> Files

    Change owner to relevant user.

  11. @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.

  12. 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

  13. 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.

  14. 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.

  15. 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.

  16. 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.

  17. 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

  18. Thanks Pinal Dave.

    Error 4064 for connecting to sql server 2005 resolved with the help of your simple process. :-)

    Thanks a lot. :-)

    Manaa

  19. 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….

  20. sir, i have deleted my default user sqlserver 2008. Now unable to login.
    Suggest any way out. Reinstall is not possible.

  21. I can see this is not new but this articles saved my critical time today. I was doing file group maintenance on a server and brought the database online. I did not know that the database for default for that server. I could not bring it online. I searched google and get to your article. I reconnected to server in SSMS using option, I selected Master and I was in and was able to bring my database online. Thanks once again. Keep up the good work.

  22. Hmm,
    I have the same problem but I cannot fix it with adding the “testdb” name in the “Connect to database” field.
    If I fill in “master” it works. But why dows it not work with “testdb” which is assigned to my “testuser”?

  23. When i try to run query for my domain id
    ALTER LOGIN [domain\user] WITH DEFAULT_DATABASE=database

    Its give me an error incorrect sentence near login in sql server 2008

  24. Alter Login not work! Why?
    I have to activate some command?

    Thanks.
    My sql server managment studio is 2005 but alter login not work.

    I enter on the database master but the command alter login not work.

    Thanks

  25. I encountered with the same problem when I change the domain/ change the password of my win login.
    To resolve this, I log-in through the SQL Authentication and remove my windows login from the SQLServer\Security\Logins. After remove, I added my windows login and it’s resolve the issue.

  26. 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

  27. This also worked for me to change user for database file
    but not the command like ALTER LOGIN [test] WITH DEFAULT_DATABASE = master

  28. 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.

  29. 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

  30. 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.

  31. I created a SQL server (2012 Standard) login account with a domain group, mydomain\abcgroup. The domain group, ‘abcgroup’, contains several domain users, and one of users is named mydomain\john. A database named ‘abcprod’ is created by a SQL super user. Under database ‘abcprod’->Security->users, I create a user name ‘abcgroup_team’, and it contains the server login account ‘mydomain\abcgroup’ above, and I gave whatever read/write permission to this username. My goal is create a login account as a domain group, and let the group leader to handle the users in the group, rather than creating each single user account in my SQL server. Can I do this? However, when john, a group member in mydomain\abcgroup, tried to log on, he got the SQL error number “4046″. Do I miss anything and how to find the problem?

    • Originally, the default database for mydomain\abcgroup was ‘mytrial’, and I had ‘abcgroup_team’ created under database ‘mytrial’->Security->users. The user ‘john’ was able to log on. Then I removed ‘mytrial’->Security->users->abcgroup_team, and create the same username under ‘abcprod’->Security->users. Then ‘john’ was not able to log on anymore. However, if I re-create ‘mytrial’->Security->users->abcgroup_team, ‘john’ was able to log on again. When ‘mytrial’->Security->users-abcgroup_team is removed, I used SQL superuser to run ALTER login [abcgroup_team] with DEFAULT_DATABASE = abcprod. It still did not help. Why was ‘john’ not able to log in if its original username ‘mytrial’->Security->users->abcgroup_team is deleted? How can I convert the login from ‘mytrial’ to ‘abcprod’?

  32. Pingback: SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video « SQL Server Journey with SQL Authority

  33. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  34. NEED HELP …….

    I am using visual basic 2008 express edition with sql server 2008…. I hv created a database in vb2008 express ed. and also added a dataset to be used in the form. But whatever I am trying to add, insert or delete into the table “Users” of my database, changes are reflected in dataset only. Once application closed, all changes are vanished…not reflected in database…why ..do i need to make some settings so that the changes are reflected in the database also once the aplication is closed….Please can somebody guide me on this……

  35. Consider one scenario you can’t able to enter in to server because your Master Database get corrupted. In this condition what will you do?

    [email removed]

  36. My solution was a little different. I tried the ALTER LOGIN [mydomain\myuser], but it did not work (The user was logging in using Windows Authentication). Instead, I used ALTER LOGIN [NT AUTHORITY\SYSTEM] and it worked

  37. Hi can you help me with another issue. I am using sql with php. Whnever I am trying to execute stored procedure I am getting below error.

    MSSQL error: Changed database context to ‘md_test’.

  38. I’m using, SQL2008, I’m able to login with Master db.
    When i’m trying to change the login to my databasse, it throws syntax errors.
    Can any body tell me the exact syntax Please?

  39. Pinal, thanks for the great tip, which helped me in the real world today.

    This happened on my SQL 2012 dev / sandbox server. I was testing disaster recovery by taking a COPY_ONLY backup of our prod database, copying it over to the dev server and timing RTO for various restore strategies. In my case I left the database in NORECOVERY mode and left for a meeting. When I came back I couldn’t connect to the server at all, and wondered what happened to the instance.

  40. 5 years on and still a very useful post! I encountered this error because a re-install of an application deleted and re-created a database, leaving the username adrift from it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s