Database Mail is a core feature in SQL Server used for sending alerts, job notifications, and custom messages. While many DBAs configure a public Database Mail profile for general use, there are scenarios where private profiles are assigned to specific SQL logins or users in msdb.

These private profiles are often part of compliance requirements, workload isolation, or application-specific configurations. However, identifying which login is associated with which private mail profile can be confusing because the relationship is stored inside the msdb database and mapped via SIDs.

This blog breaks down how Database Mail profile permissions work, how SQL Server maps logins to private profiles, and how to retrieve those associations with clean queries.

Understanding Private Database Mail Profiles

Before diving into the scripts, let’s quickly summarize how permissions work:

1. Database Mail Profile Types

Public Profile
Granted to the public role in msdb. Any DatabaseMailUserRole member can use it.
Private Profile
Granted only to a specific msdb user.
This msdb user is mapped to a SQL login through a SID (security identifier).

2. Where Permissions Are Stored

SQL Server stores Database Mail profile grants in:

msdb.dbo.sysmail_profile → Profile details
msdb.dbo.sysmail_principalprofile → Mapping between principals (logins/msdb users) and profiles
msdb.sys.database_principals → msdb users and their SIDs
sys.server_principals → server logins and their SIDs

To identify private profile associations, we must join all these metadata views.

Finding Private Database Mail Profiles Associated with Logins

Below is the most practical query DBAs use to get a complete view of private profile assignments mapped to server logins.

1. List All Private Profiles and Associated Logins

USE msdb;
GO

;WITH MapPrincipals AS
(
    SELECT 
        dp.sid                    AS msdb_user_sid,
        dp.name                   AS msdb_user_name,
        sp.principal_id           AS server_principal_id,
        sp.name                   AS login_name,
        sp.type_desc              AS login_type
    FROM sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp
        ON sp.sid = dp.sid
    WHERE dp.type IN ('S','U','G')
)
SELECT 
    p.name                       AS profile_name,
    mp.login_name,
    mp.login_type,
    mp.msdb_user_name,
    pp.is_default                AS is_default_for_login
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
LEFT JOIN MapPrincipals AS mp
    ON mp.msdb_user_sid = pp.principal_sid
WHERE 
    pp.principal_sid <> 0x00 -- Exclude public profile
ORDER BY 
    p.name, mp.login_name;

USE msdb;
GO

;WITH MapPrincipals AS
(
    SELECT 
        dp.sid                    AS msdb_user_sid,
        dp.name                   AS msdb_user_name,
        sp.principal_id           AS server_principal_id,
        sp.name                   AS login_name,
        sp.type_desc              AS login_type
    FROM sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp
        ON sp.sid = dp.sid
    WHERE dp.type IN ('S','U','G')
)
SELECT 
    p.name                       AS profile_name,
    mp.login_name,
    mp.login_type,
    mp.msdb_user_name,
    pp.is_default                AS is_default_for_login
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
LEFT JOIN MapPrincipals AS mp
    ON mp.msdb_user_sid = pp.principal_sid
WHERE 
    pp.principal_sid <> 0x00 -- Exclude public profile
ORDER BY 
    p.name, mp.login_name;

SQL

What this gives you

✔ Private profile name
✔ Login using the profile
✔ Login type (SQL, Windows)
✔ Default profile flag
✔ msdb user behind the mapping

This is the fastest way to audit or validate private mail configuration.

USE msdb;
GO

DECLARE @LoginName sysname = N'YourLoginName';

SELECT 
    p.name            AS profile_name,
    pp.is_default     AS is_default_for_login
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
JOIN sys.database_principals AS dp
    ON dp.sid = pp.principal_sid
JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE 
    pp.principal_sid <> 0x00
    AND sp.name = @LoginName
ORDER BY p.name;

USE msdb;
GO

DECLARE @LoginName sysname = N'YourLoginName';

SELECT 
    p.name            AS profile_name,
    pp.is_default     AS is_default_for_login
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
JOIN sys.database_principals AS dp
    ON dp.sid = pp.principal_sid
JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE 
    pp.principal_sid <> 0x00
    AND sp.name = @LoginName
ORDER BY p.name;

SQL

Useful when troubleshooting why a specific login cannot send mail.

3. Identify Orphaned Private Profile Assignments

This happens when the login was dropped but msdb retains the profile mapping.

USE msdb;
GO

SELECT 
    p.name AS profile_name,
    dp.name AS msdb_user_name,
    pp.is_default
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
LEFT JOIN sys.database_principals AS dp
    ON dp.sid = pp.principal_sid
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE 
    pp.principal_sid <> 0x00
    AND sp.principal_id IS NULL
ORDER BY p.name, dp.name;

USE msdb;
GO

SELECT 
    p.name AS profile_name,
    dp.name AS msdb_user_name,
    pp.is_default
FROM dbo.sysmail_principalprofile AS pp
JOIN dbo.sysmail_profile AS p
    ON p.profile_id = pp.profile_id
LEFT JOIN sys.database_principals AS dp
    ON dp.sid = pp.principal_sid
LEFT JOIN sys.server_principals AS sp
    ON sp.sid = dp.sid
WHERE 
    pp.principal_sid <> 0x00
    AND sp.principal_id IS NULL
ORDER BY p.name, dp.name;

SQL

Signs of orphans

Login dropped
msdb user still exists
Private mail rights still granted

These should be cleaned using sysmail_delete_principalprofile_sp.

Conclusion

Private Database Mail profiles in SQL Server are powerful but often misunderstood. Because these permissions are stored inside msdb and mapped via SIDs, finding the relationship between logins and mail profiles requires a proper join of system catalog views.