Login with no default database

This problem can occur when databases are dropped or renamed. Here is my query that will list all logins where the default database is missing (or offline):-

SELECT [name] Login,
       default_database_name MissingDefaultDB
FROM sys.server_principals
WHERE DATABASEPROPERTYEX(default_database_name, 'Status') IS NULL
AND default_database_name IS NOT NULL
ORDER BY [name];

Leave a comment