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

You can also return account info based on the account 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_profileaccount_sp;

Result (using vertical output):

profile_id      | 1
profile_name    | DB Admin Profile
account_id      | 1
account_name    | DB Admin
sequence_number | 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, and so just one row is returned.

Note that the sysmail_help_profileaccount_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 Account Name/ID

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

The account name is the name of the account created with the sysmail_add_account_sp stored procedure in the msdb database.

Here’s how to pass the account name:

EXEC msdb.dbo.sysmail_help_profileaccount_sp
    @account_name = 'DB Admin';

And here’s how to pass the ID:

EXEC msdb.dbo.sysmail_help_profileaccount_sp
    @account_id = 1;

Note that @account_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_profileaccount_sp
    @profile_name = 'DB Admin Profile';

And here’s how to pass the profile ID:

EXEC msdb.dbo.sysmail_help_profileaccount_sp
    @profile_id = 1;

Note that @profile_id is provided as an int.