SQL Server error - Cannot Open User Default Database error 4064

Question

SQL Server error - Cannot Open User Default Database error 4064

SQlLogic

SSCrazy Eights

Points: 9686
More actions
March 12, 2013 at 8:36 am

#394371

Any idea on this?
user is part of active directory and has windows authentication and still getting this error....any thoughts?
master is default db..
from sql error log : sql error 18456 severity 14 state 16
thanks
--------------------------------------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Double-check the login's default database. Error message is saying it's not available.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 2

I checked and master is default db and is there...

any idea?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Is the user a member of any groups that have logins in the database with different default databases?

In fact, check all the logins, see if any have a default database that no longer exists.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734489 More actions · Answer 4

Does this login have access to master? They have to be mapped to a user (or guest).

I might change it to a database that they have access in, say the one for an application.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

In fact, check all the logins, see if any have a default database that no longer exists.

I don't know if you were able to resolve this yet, but I think Gail's advice here is a good query to run and review every so often.

SELECT sl.name

FROM master.sys.server_principals sl

WHERE NOT EXISTS (SELECT 1

FROM sys.databases sd

WHERE sd.name = sl.default_database_name)

AND sl.type = 'S'

ORDER BY sl.name;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions