Default database for SQL Server login

October 24, 2023

Why do SQL Server Logins have a default database? Kenneth Fisher and Aaron Bertrand explain this here and here.

Here is a sample script to identify logins whose default database no longer exists on the server. Once you identify, you can assign another database as default to avoid the issues explained in the abovementioned two posts.

SELECT 
	ServerName=@@ServerName,
	LoginName=sp.name,
	Default_database_name
FROM sys.server_principals AS sp
LEFT JOIN sys.databases AS db 
ON sp.default_database_name=db.name
WHERE db.name IS NULL AND sp.default_database_name IS NOT NULL