SQL Server Msg 4064 — Cannot Login, Which Database Is Unavailable?
Diagnosing Msg 4064: inspecting sys.server_principals default_database_name, distinguishing from Msg 4060, DAC recovery, and the AUTO_CLOSE trap.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
Msg 4064, Level 11, State 1, Line 1
Cannot open user default database. Login failed.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'app_user'.When both lines appear together, they confirm a pivotal diagnostic fact: The user credentials are correct, and authentication succeeded! However, during session initialization, SQL Server attempted to bind the connection context to the principal's assigned default database and failed, summarily terminating the session.
▸Distinguishing Msg 4064 from Msg 4060
- •Msg 4060 (`Cannot open database requested by login`): The application connection string explicitly designated a database (
Initial Catalog=app) that is offline or unavailable (H-15). - •Msg 4064 (`Cannot open user default database`): The connection string omitted the catalog name (or specified an invalid context), falling back to the principal's configured default database, which cannot be accessed.
▸Diagnostic Workflow: Resolving the Chicken-and-Egg Impasse
1. Connect via DAC (Dedicated Administrator Connection)
The DAC connects directly to master, bypassing individual user default database restrictions:
sqlcmd -S server_name -A -E2. Query Default Database Configurations
SELECT name, default_database_name, is_disabled, type_desc
FROM sys.server_principals
WHERE name = 'app_user';Examine the catalog status:
SELECT name, state_desc, user_access_desc, is_auto_close_on
FROM sys.databases
WHERE name = 'app';Four root causes:
- 1Target database was dropped or renamed.
- 2Target database is in an unready state (
OFFLINE,RESTORING,RECOVERY_PENDING,SUSPECT). - 3Orphaned user mapping: The database exists, but contains no
USERmapped to this server login. - 4The
AUTO_CLOSEtrap: Database shuts down between connections, stalling fresh logins.
▸Step-by-Step Resolution
1. Repoint the Login's Default Database to master
Instantly restores connectivity:
ALTER LOGIN app_user WITH DEFAULT_DATABASE = master;2. Bring Target Database Online
ALTER DATABASE app SET ONLINE;3. Disable AUTO_CLOSE
ALTER DATABASE app SET AUTO_CLOSE OFF;4. Re-link Orphaned Database Users
USE app;
ALTER USER app_user WITH LOGIN = app_user;▸Related Error Guides & Hub Links
- •SQL Server Cannot Open Database 4060 (H-15) — Explicit initial catalog connection errors
- •SQL Server Login Failed 18456 (H-04) — Authentication ring buffer inspection
- •SQL Server Msg 229 Permission Denied (H-39) — Object permissions and guest user traps
- •SQL Server Hub — Connection management and disaster recovery
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
SQL Server Error 18456: Root Cause by State Code Matrix
In Error 18456, the State code reveals the actual root cause. Specific fixes for State 5, 8, 11, 18, 38, and 40, with ERRORLOG extraction scripts.
SQL Server Error 1205 Deadlock: Graph Extraction and Prevention
Extracting the deadlock graph from the system_health Extended Events ring buffer, understanding victim election, and architectural resolution.
SQL Server Pool Timeout: Connection Pool Exhaustion Fix
Difference between connection pool timeout and query command timeout, detecting connection leaks, and .NET DbContext architectural fixes.