public class ben:harrell

January 4, 2013

New updates for Cannot open user default database post

Filed under: database, SQL Server 2005, Visual Studio — Tags: , , , , , , — benjamin harrell @ 1:24 pm

After writing this post we were fortunate enough to have many people (all smarter than me) to comment with the various solutions especially for different versions so I have updated the post with the most helpful comments so that they can be found more quickly.  Good Luck!

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

January 15, 2007

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Filed under: database, dba, errors, SQL Server 2005 — Tags: , , , , , , — benjamin harrell @ 8:28 pm

Cannot open user default database. Login failed. Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

This error bugged the crap out of me tonight and I saw all kinds of crazy answers usually involving some sort of SQLCMD -blah -blah…i think this maybe the problem with command line happy developers/dbas but to make a long story short the fix is EASY and you will kick yourself for not trying this.

1.  click the options button if the options are not showing.

2.  click the connection properties tab if it is not active

3.  click on the Connect to database: dropdown

4.  type in the name of a database that still exists (ex. ‘master’)

5. connect!

I heard all kinds of rants and raves about how atrocious it was that microsoft did this to us and I agree it does suck that SQL Server doesn’t do something about this automagically but what would you suggest it do?



[UPDATE]  I wanted to move some of the great ideas in the comments to the top so that people could find other solutions more quickly:

From Commenter Fritz Laurel:


From commenter Das:

For those of you who use their Domain accounts, here is the Alter statement to run once you assign the MASTER DB from the above step.


Replace [BUILTIN\Administrators] with your [domain\usr]

NOTE:[Brackets] required on Domain\User accounts

From commenter Breb02 (SQL 2000?)

sqlcmd -E -d master (osql -E -d master for SQL 2000)
1>sp_defaultdb ‘Computer\username’, ‘master’

Later SQL Server versions:
sqlcmd -E -d master

From commenter Fuad Ahmed

And the solution for me was just switching the registration property of the server from sql authentication to windows authentication.

I think I was lucky that the login sa had different default database and the windows authentication had different.

From commenter Tony G

I’m using VS2008 Server Explorer. I opened Modify Connection, Advanced Properties, and just set User Instance to true. That allowed my local SQL Server to open the local mdf. HTH

From commenter BobG

I had to.. Under my “registered servers” list, pick the offending server/instance and then right click, choosing “properties”, then choosing “connection properties”, and then type in a database name in the offending server/instance in the “Connect to database:” drop down box.

Then I was able to get in but no offered choices from this post worked for me beyond getting back in, and I had very little rights/access.
I wound up deleting my login from the Server/instance under “Security”. Then I was able to close MS SQL Studio and went back in without further issue.
Note: I also have that same account, a domain account, that I had deleted listed under other SQL Domain Groups for the offending server/instance.

From commenter PeterG

I am running Win7x64 and once of a sudden could not open my DB anymore. I ended up having to “take ownership” of the folder with the DB inside and viola – it worked

From commenter Adrian

To repair an an SQL Server 2000 with default db detached I made the following :

1. I used SQL Server 2005 management console from a client PC and get connected to the server to another existing database;

2. Created another user :
EXEC sp_addlogin ‘Adrian’, ‘p1234′

3. Re-attached the default database with :
EXEC sp_attach_db @dbname = ‘ATOTAL’,
@filename1 = ‘C:\ATOTAL DB\atotal_Data.MDF’,
@filename2 = ‘C:\ATOTAL DB\atotal_LOG.LDF’

From commenter spiritos

I couldn’t connect either (default db not found) so tried this fix but I couldn’t even browse the server to select a new db to use. As a result I started looking at the Security for the db… turns out there was a db that was restored (just happened to be my normal login’s default db!) but in the process some Users had been dropped from the Security folder – including the group that my “normal” login belonged to! Added these groups back in then could connect again!

From commenter One IT Services

You don’t have to use any commands at all, if you still have a login that works then use that to first login to the object explorer.
If you don’t then when logging in make sure you do as stated in the blog which was to click the options button on the Connect to Server Screen.
Then on Connect to Database just click Browse Server, say yes to connect and select any database from the list.
Then once you’re in the Object Explorer, expand Security, Logins and select the username you need to fix.
At the bottom of the Login Properties box you just need to select a Default Database, most likely Master.

From commenter vishalsharma

Note : you have to type master in default database drop down. You won’t see it there in the list.


I hope some of these most appreciated comments will help others!

Blog at