SQL Server Error 4064: Cannot Open User Default Database

Diagnose the database before changing the login

Once connected through an available database, inspect the login’s current default and the state/access mode of the intended database. SQL Server exposes database state through sys.databases; Microsoft documents states including ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY and OFFLINE, plus user-access modes MULTI_USER, SINGLE_USER and RESTRICTED_USER.

Also confirm the default database assigned to the login using an authorized administrative view or login properties, and confirm the actual server/replica that received the connection. With aliases, listeners, clusters or availability groups, a database can be usable on one node yet unavailable or non-readable in the role reached by another path. The key question is not “How do I make 4064 disappear?” but “Why can this login not open its intended start database on the server context it actually reached?”

Verify: You know the intended database name, whether it exists, its current state_desc, its user_access_desc, and whether the login should still use it as the default.

Fix #2 — database renamed, dropped, or the default points to the wrong name

A rename is a classic 4064 trigger. Microsoft specifically warns that if a renamed database was a login’s default, the login can encounter Error 4064 until its default is updated to the new database name.

If the old database was intentionally renamed, assign the login to the intended renamed database. If it was dropped because an application was retired, choose the correct durable database for that login rather than mechanically assigning master.

Permission boundary: run this as an authorized administrator with permission to alter that login. Do not grant the affected application login sysadmin or securityadmin merely so it can change its own routing. If you do not administer logins on this instance, capture the intended database and hand the change to the DBA.

Verify: Start a new connection without overriding the database. It should land in the intended database and Error 4064 should not return.

Fix #3 — OFFLINE, RESTORING, RECOVERY_PENDING, SUSPECT, or another unavailable state

If the intended default database still exists but is not ONLINE, the database state is the problem to solve. Microsoft defines OFFLINE as unavailable by explicit action; RESTORING/RECOVERING as recovery states; RECOVERY_PENDING as a resource-related recovery failure requiring action; and SUSPECT as a database that could not recover successfully.

If the outage is temporary and the database is still supposed to be this login’s default, repair availability and leave the default assignment intact. Changing the login to master can make the symptom disappear while the actual database remains broken.

Verify: The intended database reaches the expected usable state and the login can start a fresh connection to it without a database override.

Fix #4 — ONLINE but SINGLE_USER or RESTRICTED_USER

An ONLINE database can still reject ordinary users because access mode is restricted. SINGLE_USER permits only one qualifying connection at a time; RESTRICTED_USER limits access to privileged database/server roles. Inspect user_access_desc before blaming the login password.

If SINGLE_USER was set for maintenance, identify whether another session is consuming the one connection and whether maintenance is still active. A monitoring agent, Object Explorer, application pool, or other background client can consume that single connection before your intended session. Do not repeatedly race those clients, kill sessions blindly, or force MULTI_USER while a restore, repair, migration, or controlled maintenance operation is still in progress. Coordinate with the DBA/maintenance owner and change access mode only when the maintenance plan says it is safe.

Verify: After the approved maintenance/access change, user_access_desc reflects the intended mode and the real login can open the database.

Fix #5 — database is ONLINE, but this login still cannot open it

If the database is ONLINE, check whether it is usable from the exact server/replica and connection intent that received the login, then whether the identity is intended to have access. ONLINE alone does not prove the database is readable for this path. Inspect the database user/principal, login-to-user SID mapping where applicable, containment model, CONNECT permission/explicit DENY, and replica/readability state before changing security principals. Do not solve an access problem by granting server-wide sysadmin.

Compare a known-good identity/path with the failing one and consider whether restore/migration/failover changed security mappings or availability context. An orphaned/mismatched user is one possible branch, not a universal explanation. Keep this separate from password troubleshooting: Error 4064 is about opening the start/default database, while Error 18456 provides broader login/authentication evidence.

Verify: the least-privileged intended identity can explicitly connect to the intended database through the same listener/instance/replica path and perform only the operations it is supposed to perform. Then test a new connection without the temporary database override.

Fix #6 — change DEFAULT_DATABASE only when the assignment itself is wrong

ALTER LOGIN ... WITH DEFAULT_DATABASE = ... is the correct durable fix when the login’s assigned default is genuinely obsolete or incorrect—for example after an intentional database rename. Microsoft documents DEFAULT_DATABASE as a login property.

Choose a durable default that matches how the login is actually used. Do not confuse the login default with an explicit application target: a connection string Initial Catalog=AppDb or Database=AppDb asks for that database explicitly and takes precedence over the login's default routing. If that explicit database is misspelled, offline, unavailable, or inaccessible, repair the explicit target or its availability/access. Changing DEFAULT_DATABASE will not repair a bad Initial Catalog.

Verify: Open a completely new connection with no temporary database override and confirm it lands where intended.

Platform boundary — SQL Server / Managed Instance vs Azure SQL Database

This article's Error 4064 and ALTER LOGIN ... DEFAULT_DATABASE workflow is primarily a SQL Server instance workflow. Azure SQL Database is database-scoped and does not support the SQL Server DEFAULT_DATABASE login option. Do not use that statement there; connect to the intended Azure SQL database explicitly and troubleshoot its user/login model with Azure SQL-specific guidance. Azure SQL Managed Instance is much closer to SQL Server's instance/login model, but still confirm the product and current syntax before making security changes.

Still seeing Error 4064?

Separate the login's default from an explicitly requested database. If SSMS works when you choose an available database but the application still fails, inspect the application's effective connection string and configuration source. Initial Catalog / Database may explicitly request a missing, misspelled, offline, or inaccessible database. In that case the explicit target is the branch to repair; changing the login default is unrelated to the application's requested database.

Official references