In SQL Server, you can use the sysmail_help_principalprofile_sp stored procedure on the msdb database to retrieve a list of all associations between Database Mail accounts and database principals.

You can also return account info based on the principal name/ID or the profile name/ID.

Return All Associations

To return all associations, simply execute the stored procedure without any parameters.

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

Result (using vertical output):

principal_id   | 25
principal_name | Marge
profile_id     | 1
profile_name   | DB Admin Profile
is_default     | 1

I’ve listed the results using vertical output so that you don’t have to scroll sideways to see all columns.

In my case, there’s just one association.

Note that the sysmail_help_principalprofile_sp stored procedure is in the msdb database and is owned by the dbo schema. Therefore, you’ll need to use three-part naming if msdb is not the current database.

Based on Principal Name/ID

You can narrow it down to just a single principal by passing the principal name or ID as a parameter.

The principal name is the name of the database user or role in the msdb database.

Here’s how to pass the principal name:

EXEC msdb.dbo.sysmail_help_principalprofile_sp
    @principal_name = 'Marge';

And here’s how to pass the principal ID:

EXEC msdb.dbo.sysmail_help_principalprofile_sp
    @principal_id = 25;

Note that @principal_id is provided as an int.

Based on Profile Name/ID

Alternatively, you can narrow it down to just a single profile by passing the profile name or ID as a parameter.

Here’s how to pass the profile name:

EXEC msdb.dbo.sysmail_help_principalprofile_sp
    @profile_name = 'DB Admin Profile';

And here’s how to pass the profile ID:

EXEC msdb.dbo.sysmail_help_principalprofile_sp
    @profile_id = 1;

Note that @profile_id is provided as an int.