sysmail_help_profileaccount_sp (Transact-SQL)

[ @profile_id = ] profile_id

Is the profile ID of the profile to list. profile_id is int, with a default of NULL. Either profile_id or profile_name must be specified.

[ @profile_name = ] 'profile_name'

Is the profile name of the profile to list. profile_name is sysname, with a default of NULL. Either profile_id or profile_name must be specified.

[ @account_id = ] account_id

Is the account ID to list. account_id is int, with a default of NULL. When account_id and account_name are both NULL, lists all the accounts in the profile.

[ @account_name = ] 'account_name'

Is the name of the account to list. account_name is sysname, with a default of NULL. When account_id and account_name are both NULL, lists all the accounts in the profile.

0 (success) or 1 (failure)

Returns a result set with the following columns.

When no profile_id or profile_name is specified, this stored procedure returns information for every profile in the instance.

The stored procedure sysmail_help_profileaccount_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database is not msdb.

Execute permissions for this procedure default to members of the sysadmin fixed server role.

A. Listing the accounts for a specific profile by name

The following example shows listing the information for the AdventureWorks Administrator profile by specifying the profile name.

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp
   @profile_name = 'AdventureWorks Administrator'

Here is a sample result set, edited for line length:

profile_id  profile_name                 account_id  account_name         sequence_number
----------- ---------------------------- ----------- -------------------- ---------------
131         AdventureWorks Administrator 197         Admin-MainServer     1
131         AdventureWorks Administrator 198         Admin-BackupServer   2

B. Listing the accounts for a specific profile by profile ID

The following example shows listing the information for the AdventureWorks Administrator profile by specifying the profile ID for the profile.

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp
    @profile_id = 131 ;

Here is a sample result set, edited for line length:

profile_id  profile_name                 account_id  account_name         sequence_number
----------- ---------------------------- ----------- -------------------- ---------------
131         AdventureWorks Administrator 197         Admin-MainServer     1
131         AdventureWorks Administrator 198         Admin-BackupServer   2

C. Listing the accounts for all profiles

The following example shows listing the accounts for all profiles in the instance.

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp

Here is a sample result set, edited for line length:

profile_id  profile_name                 account_id  account_name         sequence_number
----------- ---------------------------- ----------- -------------------- ---------------
131         AdventureWorks Administrator 197         Admin-MainServer     1
131         AdventureWorks Administrator 198         Admin-BackupServer   2
106         AdventureWorks Operator      210         Operator-MainServer  1

Reference

Concepts