On this page
Why it happens
Error 4064 is not a password problem. The login itself was accepted, but every SQL Server connection must land in some database first, and each login has a default database assigned to it. If that default database cannot be opened, the connection is rejected outright with error 4064.
The database usually cannot be opened for one of a few reasons: it was DROPped, it was taken OFFLINE or is in recovery, it was renamed, or the login lost access to it (its user mapping was removed or the database was restored without the login). Because the failure happens before a database context exists, you cannot simply run USE master to escape it. See SQL Server security for how logins and database access fit together.
This is easy to confuse with error 4060. Error 4060 is about the database you explicitly asked to open in the connection string; error 4064 is about the login default database that SQL Server tries to open when you do not name one. The fix is to override that default with a database that is guaranteed to be reachable.
Examples
Login default database was taken offline
-- AppUser has DEFAULT_DATABASE = Sales.
-- An admin takes Sales offline for maintenance:
ALTER DATABASE Sales SET OFFLINE;
-- AppUser now connects with no database named.
-- SQL Server tries to open Sales (the default) and fails.Cannot open user default database. Login failed. Login failed for user 'AppUser'. (Microsoft SQL Server, Error: 4064)
Login lost access to its default database
-- The database user mapping for AppUser was dropped:
USE Sales;
DROP USER AppUser;
-- The login still has DEFAULT_DATABASE = Sales,
-- but can no longer open it, so 4064 is raised on connect.Cannot open user default database. Login failed. Login failed for user 'AppUser'. (Microsoft SQL Server, Error: 4064)
How to fix it
Connect while overriding the default database
Get in by naming a database you can definitely open, such as master, instead of letting SQL Server use the broken default. With sqlcmd use the -d switch; in a connection string set Initial Catalog (or Database) to a reachable database.
-- sqlcmd: force the initial database to master
sqlcmd -S localhost -U AppUser -P *** -d master
-- ADO.NET / connection string equivalent:
-- Server=localhost;User Id=AppUser;Password=***;Initial Catalog=master;Point the login default at a database that always exists
Once you are connected to master, reset the login default database to one that is always online. After this the login can connect again without naming a database.
ALTER LOGIN [AppUser] WITH DEFAULT_DATABASE = master;Bring the original database back or grant access again
If the default database is simply offline, bring it online. If the login lost access, re-create its user mapping in that database so the original default works again.
-- Database was offline: bring it back
ALTER DATABASE Sales SET ONLINE;
-- Access was lost: re-map the login inside the database
USE Sales;
CREATE USER AppUser FOR LOGIN AppUser;In SSMS, specify a database on the connect dialog
SQL Server Management Studio lets you override the default without any command line. On the Connect to Server dialog click Options, open the Connection Properties tab, and set Connect to database to master. Connect, then run the ALTER LOGIN above.
-- No SQL needed for the dialog itself.
-- After connecting to master, fix the default:
ALTER LOGIN [AppUser] WITH DEFAULT_DATABASE = master;How to prevent it
Set the default database for application and administrative logins to one that always exists, such as master, so a single database going offline can never lock a login out of the whole instance. Reserve non-master defaults only for logins whose database is guaranteed to stay online.
When you drop, rename, restore, or take a database offline, review which logins use it as their default first and repoint them. Keeping the application database online and its user mappings intact is what keeps 4064 from appearing. See SQL Server security for managing logins and database access.
Common questions
How do I fix SQL Server error 4064?
Connect while overriding the broken default database (sqlcmd -d master, or set Initial Catalog to master in the connection string), then run ALTER LOGIN [AppUser] WITH DEFAULT_DATABASE = master. If the original default is just offline, bring it back with ALTER DATABASE ... SET ONLINE, or re-create the login user mapping if access was lost.
What is the difference between error 4064 and error 4060?
Error 4060 is raised for the database you explicitly requested in the connection (the one you asked to open), while error 4064 is raised for the login default database that SQL Server opens when you do not name one. Both mean a database could not be opened, but 4064 specifically points at the login DEFAULT_DATABASE setting.
Why can I not just run USE master to get past error 4064?
The failure happens before any database context is established, so the connection never opens and there is nowhere to run USE master. You have to name a reachable database in the connection itself, for example with sqlcmd -d master or Initial Catalog=master, and only then can you run commands.