Quick Scan Report – Login With Bad Default Database

What this check looks for

Enabled logins in sys.server_principals whose default_database_name does not appear in sys.databases. The message names the login.

Why it matters

The login is broken, and the error it produces does not say so.

When a client connects without naming a database, SQL Server uses the login’s default database. If that database does not exist, the connection fails with:

Error 4064: Cannot open user default database. Login failed.

That message is misleading in exactly the way that costs time. It says “login failed”, which sends people to check the password, the account state, and the authentication mode. The credentials are fine. The database the login wants to land in is gone.

And whether it fails at all depends on the client, which is why this is intermittent and confusing:

• A connection string with Initial Catalog or Database= specified works normally, because the client names the database and the default is never consulted.
• A connection string without it fails, every time.
• SQL Server Management Studio fails unless you set the database explicitly in the connection options, which is a setting most people do not know is there.
• A linked server, a job step, or a tool with its own connection handling may go either way.

So the same login works for the application and fails for the person trying to troubleshoot it, or works everywhere except the one nightly process that does not specify a database.

How it happens is almost always the same story: a database was dropped, renamed, restored under a different name, or moved to another instance, and the logins pointing at it were never updated. Nothing prevents dropping a database that logins default to, and nothing warns about it.

There is a security angle too. A login nobody can use is often a login nobody has reviewed. Finding these usually surfaces accounts for people who left, or for applications that were decommissioned, and those should be disabled rather than repaired.

How to confirm it yourself

The logins concerned:

SELECT sp.[name]                  AS [login_name],
       sp.[type_desc],
       sp.[default_database_name],
       sp.[is_disabled],
       sp.[create_date],
       sp.[modify_date]
  FROM sys.server_principals AS sp WITH (NOLOCK)
 WHERE sp.[type] IN ('S', 'U', 'G')
   AND sp.[is_disabled] = 0
   AND sp.[default_database_name] IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM sys.databases AS d WITH (NOLOCK)
                    WHERE d.[name] = sp.[default_database_name])
 ORDER BY sp.[name];

Check disabled logins too, separately, because those are the ones to consider removing rather than repairing:

SELECT [name], [default_database_name], [is_disabled], [modify_date]
  FROM sys.server_principals WITH (NOLOCK)
 WHERE [type] IN ('S', 'U', 'G')
   AND [default_database_name] IS NOT NULL
   AND [default_database_name] NOT IN (SELECT [name] FROM sys.databases WITH (NOLOCK));

Where the login can actually go, which is what you need to choose a replacement default:

SELECT dp.[name]      AS [database_user],
       DB_NAME()      AS [database_name],
       sp.[name]      AS [login_name]
  FROM sys.database_principals AS dp WITH (NOLOCK)
 INNER JOIN sys.server_principals AS sp WITH (NOLOCK) ON sp.[sid] = dp.[sid]
 WHERE dp.[type] IN ('S', 'U', 'G');

Run that in each database, or across all of them:

EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4 AND HAS_DBACCESS(DB_NAME()) = 1
SELECT DB_NAME() AS [database_name], sp.[name] AS [login_name], dp.[name] AS [database_user]
  FROM sys.database_principals  AS dp WITH (NOLOCK)
 INNER JOIN sys.server_principals AS sp WITH (NOLOCK) ON sp.[sid] = dp.[sid]
 WHERE dp.[type] IN (''S'', ''U'', ''G'');';

Whether anybody is still trying to use it, from the error log:

EXEC xp_readerrorlog 0, 1, N'Cannot open user default database';

Recent entries mean something is actively failing. No entries at all usually means the login is unused, which points at disabling rather than fixing.

How to fix it

Either point the login at a database it can use, or disable it. Which one depends on whether it is still needed.

To repair it:

ALTER LOGIN [DOMAIN\SomeUser] WITH DEFAULT_DATABASE = [TheDatabaseTheyActuallyUse];

Choose the database the login has a user in, from the query above, not an arbitrary one. Setting the default to a database the login cannot access replaces error 4064 with error 4060, which is the same problem wearing a different number.

master is the safe fallback and a poor habit. Every login can connect to master, so it always works, and it means anybody connecting interactively starts in the wrong place and may create objects there by accident. Use it when there is genuinely no right answer, not as the default choice.

To retire it instead:

ALTER LOGIN [OldAppLogin] DISABLE;

Disable before dropping. A disabled login can be re-enabled in seconds if something turns out to need it; a dropped one has to be recreated, and for a SQL login that means a new SID and orphaned users everywhere it was mapped.

Then check for the wider damage, because a dropped database leaves more than this behind:

-- jobs that reference the missing database
SELECT j.[name] AS [job_name], s.[step_name], s.[database_name], s.[command]
  FROM msdb.dbo.sysjobsteps AS s
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = s.[job_id]
 WHERE s.[database_name] NOT IN (SELECT [name] FROM sys.databases);

And fix the process. Dropping a database should include a step that checks which logins default to it:

SELECT [name] FROM sys.server_principals
 WHERE [default_database_name] = 'TheDatabaseAboutToBeDropped';

Running that before the drop turns this finding into something that never occurs.

How long it takes

About an hour for an instance, most of it establishing which logins are still needed. The ALTER LOGIN itself is instant and needs no restart or disconnection.

Related reports

Related checks

Frequently asked questions

The application works, so why does this matter? Because its connection string names a database. Anything that connects without naming one, including a person using Management Studio to troubleshoot, fails with a misleading message.

Should I set them all to master? Only where there is no better answer. master always works, which is why it is tempting, and it means interactive users start in the wrong database and can create objects there by mistake.

Can I fix this without disconnecting anyone? Yes. ALTER LOGIN ... WITH DEFAULT_DATABASE takes effect for new connections and does not disturb existing ones.

The login has no user in any database. Then it cannot do anything useful anywhere, and disabling it is the right answer rather than choosing a default for it.