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

« SQLAuthority News – SQL Server Security Whitepapers

SQL SERVER – Clear Drop Down List of Recent Connection From SQL Server Management Studio »

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

November 4, 2008 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 137 Comments

137 Responses

on November 4, 2008 at 9:38 pm | Reply SJ

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.
on December 28, 2008 at 10:43 am | Reply Michael Baker

I tried your fix, but I still get the login failure
on January 9, 2009 at 9:14 am | Reply Thankful

Thanks for the tip. It worked great!!!!!!
on January 14, 2009 at 8:37 am | Reply Stewart Celani

Thanks for this. I locked myself out of my local test account and this fixed it.
on January 15, 2009 at 6:52 am | Reply Tom

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
on January 15, 2009 at 10:48 pm | Reply Matt

Thank you!!!!
on January 27, 2009 at 6:05 pm | Reply wwd

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
on January 27, 2009 at 11:17 pm | Reply Brian

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.
on March 17, 2009 at 6:05 am | Reply Lloyd R

Thanks, worked. And much simpler than Microsoft’s KB. Used it in SqlExpress2008
on April 1, 2009 at 9:06 pm | Reply Keem

Thanks so much. You were really more than helpful….
on April 2, 2009 at 11:29 am | Reply krishna prakash

Dev
This is the best solution i fount the google.
Thanks
on April 4, 2009 at 2:53 am | Reply Keem

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.
]
on May 5, 2009 at 11:28 pm | Reply Chris

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.
- on August 31, 2010 at 7:20 pm | Reply Diana
  
  i agree… spot on ! chris
on May 7, 2009 at 7:53 am | Reply KatyGirl

So simple !! thanks that worked a treat !
on May 16, 2009 at 6:09 pm | Reply sma

thank u for ur help
on June 4, 2009 at 7:27 am | Reply Ross

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
on June 5, 2009 at 2:43 am | Reply Keem

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 ?
on June 24, 2009 at 6:30 pm | Reply MarkTab

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
on July 8, 2009 at 7:58 pm | Reply Curvian Vynes

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

Database properties -> Files

Change owner to relevant user.
- on January 21, 2013 at 9:06 pm | Reply Drew
  
  This is what I needed – THANKS!
on July 9, 2009 at 1:25 am | Reply Abel

Many thanks….Easy, clean and simple solution!!!!
on July 19, 2009 at 12:57 am | Reply Kirk

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.
on July 19, 2009 at 10:41 pm | Reply Imran Mohammed

@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.
on July 25, 2009 at 7:27 am | Reply Moses

Thanks heaps for this, thought i’d stuffed it!
on July 28, 2009 at 4:44 pm | Reply Anita

thanks,
it worked :)
on October 23, 2009 at 7:36 pm | Reply Mark

Saved my skin there mate. Cheers!
on November 8, 2009 at 4:15 am | Reply DeDoS

Thanks…
Work!
on December 2, 2009 at 8:23 pm | Reply muralekarthick

Thank you, It helped me a lot, made me to recover from panic ………
on December 16, 2009 at 1:19 am | Reply Sean

Thank you Ross, I was about ready to commit seppuku there for a bit.
on January 8, 2010 at 8:54 am | Reply mnjrupp

This helped me! Thanks
on January 20, 2010 at 8:52 am | Reply cesar_ad

Muchas gracias!!!
on February 16, 2010 at 8:34 am | Reply Das Gajula

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
on March 23, 2010 at 6:37 pm | Reply Vijay Kumar

Many thanks for the valuable and its really a very good post
on March 30, 2010 at 1:54 am | Reply AT

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.
on April 20, 2010 at 7:55 am | Reply Mohammad Yusuf

Thank you !!! I stuck with this problem for five days.
on April 22, 2010 at 6:20 pm | Reply I2C

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.
on May 4, 2010 at 11:03 pm | Reply Andrew Wright

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.
on May 18, 2010 at 6:03 pm | Reply asif

thanxa
it work’s
i love u
on May 27, 2010 at 1:00 am | Reply Bill S

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.
on June 20, 2010 at 12:20 pm | Reply vel

Thank you so much! It worked very much.
on July 2, 2010 at 2:13 pm | Reply Mamunur Rashid

Thanks a lot.
Fantastic solution !!
on July 16, 2010 at 11:56 am | Reply Kamamikaza

Thank so lots…..^_^
on August 16, 2010 at 2:25 pm | Reply Teo

Many thanks. It worked great!!!
on August 31, 2010 at 7:14 pm | Reply Diana

Hi,
v have always solved my problems. Thanks a ton
on September 21, 2010 at 1:15 am | Reply Robert

thank u for ur help
on September 27, 2010 at 5:14 pm | Reply Mehboob Ali Yousafzai

Thnx,it solved my problem…many thnx
on September 28, 2010 at 11:15 pm | Reply Poramaporn

Thank you so much..
on October 1, 2010 at 6:26 pm | Reply KAGS

Thanks, the tip was very useful.
on November 2, 2010 at 8:25 pm | Reply Kirtan

Worked great!
on November 30, 2010 at 7:23 pm | Reply navin

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
on December 27, 2010 at 11:52 am | Reply Pablo Alejuandro Perez Acosta

VERY THANKS MY DEAR FRIEND!
on February 17, 2011 at 7:56 am | Reply irfan

nice. thanks my Database experts was unable to help me. thanks. :)
on March 21, 2011 at 12:49 pm | Reply guru

Working Great thnx..!!
on April 13, 2011 at 11:50 am | Reply Manaa

Thanks Pinal Dave.

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

Thanks a lot. :-)

Manaa
on May 30, 2011 at 4:32 pm | Reply Anuja

Thanks Pinal Dave.

It worked :)
on June 7, 2011 at 9:54 am | Reply Ravi

Thx champ. It works as described.

I was a bit worried as I had Detached the DB for my login!

nice.
on June 23, 2011 at 12:10 pm | Reply UsrMike89

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….
on August 3, 2011 at 5:25 pm | Reply Stevo

Options allowed me to connect.

Then ran :

ALTER LOGIN [domain\userGroup] WITH DEFAULT_DATABASE = master
GO

Thanks
on August 25, 2011 at 1:06 pm | Reply Igor68

Thanks for the help. It worked, OK !!!!!!
on August 30, 2011 at 10:32 am | Reply harish k gora

sir, i have deleted my default user sqlserver 2008. Now unable to login.
Suggest any way out. Reinstall is not possible.
on September 8, 2011 at 1:47 pm | Reply Winston

Thanks it worked.
on September 9, 2011 at 4:05 am | Reply Leandro

Thank you !
on September 14, 2011 at 8:12 am | Reply Mayur

Thanks a lot…saved my a**
on September 30, 2011 at 2:57 am | Reply Ameena Lalani (@SQLHands)

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.
on November 14, 2011 at 9:44 am | Reply sandy

thanks for that , it really worked well !
on November 19, 2011 at 10:52 am | Reply Kenos

Thanks, worked for me !
on November 20, 2011 at 1:21 pm | Reply mohit joshi

mohit,

hi, i did the same as u told but unable to take backup offline
on December 20, 2011 at 4:51 pm | Reply Abdul Jabbar Patel

solved my problem.. it work fine
lot of thank Pinal

nice post

Thanks again

Abdul Jabbar Patel
on December 28, 2011 at 8:09 pm | Reply abhi

worked like a charm. quick and easy.

thanks pinal
on January 2, 2012 at 4:11 pm | Reply Sunil Sharma

Thanks a lot.

It’s really a great post.

Sunil Sharma
on January 15, 2012 at 8:50 pm | Reply IT user

Thanks!
on January 20, 2012 at 3:34 pm | Reply hawkmaster

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”?
on February 7, 2012 at 5:51 pm | Reply Raúl

thanks very very much, I was going crazy…
on February 20, 2012 at 12:25 pm | Reply Jacques

thanks, very helpful!
on February 29, 2012 at 8:16 pm | Reply Abhishek.

Thanks, this help me to solve the problem.
on March 1, 2012 at 11:38 am | Reply Fahad Sattar

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
on March 6, 2012 at 3:39 pm | Reply waqas mehmood

It worked great sir, thnx for the solution
on March 14, 2012 at 4:14 pm | Reply SIMY THOMAS

THANKS A LOT ..IT WORKED!!:D
on March 14, 2012 at 6:39 pm | Reply tg19

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
on March 23, 2012 at 12:17 pm | Reply Debdutta Nath

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.
on March 26, 2012 at 6:27 pm | Reply Aarti

This post saved my day today ! Thanks a lot.
on April 1, 2012 at 2:18 pm | Reply alwaleed

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
on April 18, 2012 at 2:56 pm | Reply Soumen

This also worked for me to change user for database file
but not the command like ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
on April 27, 2012 at 10:51 am | Reply amrut

Thank you
on April 27, 2012 at 5:43 pm | Reply Jyoti

Fantastic , it worked
on May 9, 2012 at 10:14 am | Reply maganti

Thank You
on May 9, 2012 at 6:31 pm | Reply Anand Javali

Thank you…! it worked for me…
on May 17, 2012 at 1:02 pm | Reply Rohit

Thanks lot.. i was finding for it.. but after login, when i am adding new database it is giving error..
on May 21, 2012 at 10:13 pm | Reply Toño

Thank you !!
on May 22, 2012 at 12:17 am | Reply Eric Russell

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.
on June 15, 2012 at 12:58 pm | Reply Rohan

Kudos Pinal… thanks!
on June 18, 2012 at 11:40 am | Reply mehulrajgor

Thanks Its worked…
on June 18, 2012 at 11:41 am | Reply mehulrajgor

Thanks Its worked
on June 24, 2012 at 11:44 pm | Reply eldersoto

Thanks from Nicaragua, its worked.
on June 24, 2012 at 11:45 pm | Reply eldersoto

Muchas gracias, thanks from Nicaragua.
on June 26, 2012 at 8:29 pm | Reply andrew adams nyondo

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
on July 14, 2012 at 4:58 pm | Reply Fernando

Thanks, this solution solve my problem
on July 17, 2012 at 3:23 pm | Reply Miguel

Solved my problem!! Thx, i’ve been using your solutions for a log time and never expressed my gratitude.
on July 19, 2012 at 6:40 pm | Reply Super

Thank you soo much!!!
on July 20, 2012 at 9:42 pm | Reply Sam

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.
on July 23, 2012 at 5:40 pm | Reply stdInput

Nice!
on August 2, 2012 at 10:10 am | Reply ADT

WAW! IT WORKED..
on August 10, 2012 at 5:43 am | Reply Keith

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?
- on August 10, 2012 at 5:47 am | Reply Keith
  
  Sorry, I meant “4064″ error. My typo for “4046″.
- on August 10, 2012 at 10:55 am | Reply Keith
  
  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’?
on August 22, 2012 at 11:18 am | Reply Ajit

Thanks for this.
on September 1, 2012 at 2:37 pm | Reply Ramakrishna Dwadasi

Thanks Gr8…
on September 18, 2012 at 8:52 am | Reply Mahendra

Thanks a lot …its saved my time…
on October 19, 2012 at 10:47 am | Reply Jija

Thank you sir.
on October 24, 2012 at 7:01 am | Reply SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video « SQL Server Journey with SQL Authority

[...] SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed f… [...]
on October 24, 2012 at 8:19 am | Reply pinaldave

Watch SQL in Sixty Seconds Video to resolve connection error http://bit.ly/connection-error
on October 24, 2012 at 4:50 pm | Reply nilesh

Pinal You are guineus…
You proved god for me today….
on November 3, 2012 at 7:01 am | Reply SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

[...] If the user is not able to login into SQL Server due to any error or issues there were two different blog post addresses the same issue here and here. [...]
on November 19, 2012 at 11:45 am | Reply jigar mistri

Thank you pinal fabulous
on November 19, 2012 at 2:36 pm | Reply jarana

Thaaaaaaaanks a lot!
on November 19, 2012 at 2:37 pm | Reply juanjoarana

Yes! Thanks!
on November 28, 2012 at 1:07 pm | Reply Sam

Thanks ! appreciate a ton.
on January 2, 2013 at 6:38 pm | Reply Kunal

Thanks a lot Pinal, it fixed the issue for me.
on January 10, 2013 at 9:22 am | Reply Chinh Tran

Thanks so much. good solution!
on January 28, 2013 at 12:06 am | Reply Seema Mongia

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……
on February 24, 2013 at 7:05 am | Reply Athena

I have the problem above but my DAC is disabled. How can I solve this problem?
on March 7, 2013 at 12:13 pm | Reply sathishkumar

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]
on March 7, 2013 at 11:55 pm | Reply Frank

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
on March 9, 2013 at 7:01 am | Reply Jason

Pinal Dave, that did the trick. You Rock! Love your website!
on March 14, 2013 at 9:30 pm | Reply AHPerez

Thanks for the tip, you solved my problem!
on March 30, 2013 at 11:10 am | Reply Uwais

Thank you soo much!!!
on April 2, 2013 at 5:50 am | Reply Shawn

Thank you so much for this!
on April 5, 2013 at 4:55 pm | Reply Gabor

Many thanks for your help!
on April 8, 2013 at 3:08 pm | Reply Meghavi

Thank you very much. I have not found so quick solution solution.
on April 8, 2013 at 3:11 pm | Reply Meghavi

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’.
on April 16, 2013 at 1:04 am | Reply alan short

Saved my life yet again… don’t know why you do it but keep doing what you do do!!!
on April 17, 2013 at 1:20 am | Reply E.

thank you!!
on April 27, 2013 at 12:04 am | Reply ajay

Great :-)
Thanks:-)
on April 30, 2013 at 1:23 pm | Reply Divya

it works,,,thanks
on May 7, 2013 at 12:32 pm | Reply Raz974

Well done!!
on May 13, 2013 at 5:34 pm | Reply Larry

Worked Famously!!! Thank you