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

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

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

72 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 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”?