In this post I’m going to discuss how to find the sql server default database for a user. It should be noted first that ‘user’ in the title is a bit of a misnomer. It is possible to determine a default database for a specific login (certificate, asymmetric key, windows, sql), role or group but not a particular ‘user’. It is often useful to know the default database for a given login, role or group since it can make building queries easier. There is a catalog view named sys.server_principals that can be queried to find the desired information. Further details can be found here.
SQL
select name, default_database_name from sys.server_principals;
Output
name default_database_name -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- sa master public NULL sysadmin NULL