Cannot Open Database Requested by the Login (Errors 4060 and 4064)

Or its close relative:

Cannot open user default database. Login failed.
Msg 4064, Level 11, State 1

The login is fine. Both of these get reported as “login failed” and both get treated as a password problem, and in neither case is the password involved. Authentication succeeded. The database is the problem.

Which One You Have Matters

The two look almost identical and have different fixes:

4064 is the one that locks people out of SSMS, because SSMS uses the login’s default database and will not connect if it cannot open it.

Check the Database First

SELECT  name,
        state_desc,
        user_access_desc,
        is_read_only,
        collation_name
FROM    sys.databases
WHERE   name = N'YourDb';

If state_desc is anything other than ONLINE, that is your answer and no amount of permission work will help. RESTORING, RECOVERY_PENDING, SUSPECT and OFFLINE all produce a login failure that looks like a permissions problem.

If user_access_desc is SINGLE_USER, somebody else is already connected and everyone else is locked out.

No row at all means the database does not exist on this instance. Check you are on the server you think you are on, because that is more often the answer than a dropped database.

Then Check the Mapping

The database exists and is online, so the login has no user in it:

USE YourDb;
GO
SELECT  dp.name        AS database_user,
        dp.type_desc,
        sp.name        AS server_login,
        dp.sid
FROM    sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE   dp.type NOT IN ('R', 'A')
ORDER   BY dp.name;

A user with no matching server login is an orphaned user, usually left behind by a restore from another server. That is its own problem and its own fix.

Grant access properly rather than adding them to db_owner to make it go away:

USE YourDb;
GO
CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user];
ALTER ROLE db_datareader ADD MEMBER [DOMAIN\user];

Fixing 4064 Specifically

The login cannot open its default database, so it cannot connect at all. The fix is on the login and does not require touching the database:

ALTER LOGIN [DOMAIN\user] WITH DEFAULT_DATABASE = [master];

If you are locked out of SSMS yourself, you can still get in by specifying a database you can reach: on the connection dialog, Options → Connection Properties → Connect to database, and type master. Then run the ALTER LOGIN above.

sqlcmd does the same thing with -d:

sqlcmd -S YOURSERVER -d master

When It Happens to Everyone at Once

If every application starts reporting 4060 simultaneously, stop looking at logins. Something happened to the database:

-- what state is everything in, and did something change recently?
SELECT  name, state_desc, user_access_desc, is_read_only
FROM    sys.databases
WHERE   state_desc <> 'ONLINE' OR user_access_desc <> 'MULTI_USER';

A database left in SINGLE_USER after maintenance is the classic version of this, and it locks out everyone except whoever holds the one connection.

Stopping It Recurring

• Do not set an application database as anyone’s default. Set master, so a database problem never becomes a login problem.
• Check for orphaned users after every restore from another server. The mapping is by SID, and SIDs do not survive the move.
• Alert on databases not in ONLINE and MULTI_USER. It catches the single-user-after- maintenance case before an application does.

Common Questions

Related Scripts

• Get Permissions and Role Membership, effective access for one login across the instance
• Get Database Inventory, which databases exist and what state they are in
• Login Failed (Error 18456), for when authentication really is the problem