SQL Server Error 4064 Fix: Cannot Open Default Database

What error 4064 actually means

Every SQL Server login has a configured default database — the database a session lands in automatically when no other database is specified. Error 4064 fires when SQL Server tries to open that database as part of completing the login, and fails, for any reason. Critically, the credentials themselves are usually completely fine — this is a database-availability problem wearing a login-failure error message.

Cannot open user default database. Login failed.
Login failed for user 'app_user'. (Microsoft SQL Server, Error: 4064)

Common causes

• The default database was dropped. Someone deleted the database the login was assigned to, without changing the login's default database first.
• The database was detached or renamed. Same net effect as dropping — SQL Server can't find a database by that name anymore.
• The database is offline, in recovery, or in single-user mode. It technically still exists, but isn't in a state that can be opened for a new connection right now.
• The login lost access to the database. A user mapping was removed, or an orphaned user situation exists after a restore (see Fix 3 below).
• The database was restored to a different server or instance where the same-named database doesn't exist, but the login's default database setting still points at the old name.

Fix 1 — Log in by overriding the connection database

If you're completely locked out, the fastest path back in is bypassing the broken default entirely:

• In SSMS: on the "Connect to Server" dialog, click Options, go to the Connection Properties tab, and set "Connect to database" to master (or any database you know is online).
• Via sqlcmd: add the -d flag explicitly.

sqlcmd -S your_server -U your_login -P your_password -d master

This gets you a working session without touching the login's actual configuration — the next step is making that fix permanent.

Fix 2 — Change the login's default database permanently

Once connected (via Fix 1, or from an administrator account that isn't affected), update the login's default database directly:

ALTER LOGIN [app_user] WITH DEFAULT_DATABASE = master;

Point it at whatever database is actually intended and currently available — master is the safe universal fallback since it's essentially never dropped or taken offline.

Fix 3 — Fix an orphaned user after a restore

If this started right after restoring a database from another server, the login and the database user inside it may no longer be linked, even though both individually exist:

-- Find orphaned users in the restored database
EXEC sp_change_users_login 'Report';

-- Re-link a specific orphaned user to its login
ALTER USER app_user WITH LOGIN = app_user;

sp_change_users_login is deprecated in favor of ALTER USER ... WITH LOGIN in current SQL Server versions, but the 'Report' mode is still a fast way to spot which users are orphaned before fixing them individually.

Preventing it from happening again

• Set administrative logins' default database to master — it removes this entire failure mode for the accounts that most need to be reliably reachable.
• Change a login's default database before dropping or renaming the database it points to, as a standard step in any decommissioning checklist.
• After restoring to a new server, immediately check for orphaned users rather than waiting for a login failure to surface the problem.

Common mistakes

• Assuming the password is wrong. Error 4064 looks like a login failure but is almost never a credentials problem — resetting the password won't fix it.
• Dropping a database without checking which logins default to it first. This is the single most common way to trigger this error for other people.
• Fixing the immediate lockout with Fix 1 but never applying Fix 2. The override in Fix 1 only lasts for that one connection — without updating the login, the same failure recurs on the next login attempt.
• Confusing an orphaned user with a missing login entirely. They require different fixes — sp_change_users_login/ALTER USER ... WITH LOGIN for orphaned users, versus creating a new login from scratch if the login itself doesn't exist.