Fix 'Cannot open user default database' Error in SQL Server

1. The Default Database Was Deleted or Renamed

This is the most common cause. Someone (maybe you) deleted the database that was set as the default for the login. Or they renamed it. SQL Server can't find it, so the login fails before you even see the object explorer.

You need to connect to SQL Server using a different database context. Do this in SSMS by going to Options > Connection Properties and setting the 'Connect to database' field to 'master'. If you can get in that way, just run:

ALTER LOGIN [YourLoginName] WITH DEFAULT_DATABASE = master;

That's the fix. The login will now default to master, which always exists. You can change it back to the correct database after you restore or recreate it.

If you can't get SSMS to open at all, use SQLCMD from the command line:

sqlcmd -S YourServerName -E -d master
GO
ALTER LOGIN [YourLoginName] WITH DEFAULT_DATABASE = master;
GO
EXIT

For SQL authentication, add -U and -P with the credentials. This works every time unless the server itself is hosed.

2. The Default Database Is Offline or in Single-User Mode

If the database exists but is offline, SQL Server still can't authenticate you. Same with single-user mode — another connection might be hogging it.

First, check the database status. Connect to master (same trick as above) and run:

SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

If it shows OFFLINE or SINGLE_USER, you've found the problem. To bring it online:

ALTER DATABASE YourDatabaseName SET ONLINE;

For single-user mode, kill the blocking session and set it back to multi-user:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

If you can't kill the session, use:

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Don't bother with sp_who or sp_who2 — they're outdated. I use sys.dm_exec_sessions for this:

SELECT session_id, login_name FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName');
KILL session_id;

Replace session_id with the actual number from the query.

3. The Login Has No sysadmin Role and Lacks Permission on the Default Database

Rarer but it happens. A non-sysadmin login has a default database they don't have the CONNECT permission on. When SQL Server tries to open that database, it can't, and the login fails security checks.

You need to grant CONNECT permission on that database. Connect as a sysadmin to master, then switch to the problem database:

USE YourDatabaseName;
GO
GRANT CONNECT TO YourLoginName;
GO

If the database is fine but permissions are missing, this is your fix. If you can't grant CONNECT (maybe the login is orphaned), fix the orphan first:

USE YourDatabaseName;
GO
EXEC sp_change_users_login 'Auto_Fix', 'YourLoginName';

This remaps the database user to the server login. Run it from the database context, not master.

Quick-Reference Summary