Share via

  • Article

sysmail_help_profileaccount_sp (Transact-SQL)

Lists the accounts associated with one or more Database Mail profiles.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sysmail_help_profileaccount_sp
   {   [ @profile_id = ] profile_id 
      | [ @profile_name = ] 'profile_name' }
   [ , {   [ @account_id = ] account_id
         | [ @account_name = ] 'account_name' } ]

Arguments

  • [ @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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Returns a result set with the following columns.

Column name

Data type

Description

profile_id

int

The profile ID of the profile.

profile_name

sysname

The name of the profile.

account_id

int

The account ID of the account.

account_name

sysname

The name of the account.

sequence_number

int

The sequence number of the account within the profile.

Remarks

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.

Permissions

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

Examples

A. Listing the accounts for a specific profile by name

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

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp
   @profile_name = 'AdventureWorks2008r2 Administrator';

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

profile_id  profile_name                 account_id  account_name         sequence_number
----------- ---------------------------- ----------- -------------------- ---------------
131         AdventureWorks2008R2 Administrator 197         Admin-MainServer     1
131         AdventureWorks2008R2 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 AdventureWorks2008R2 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         AdventureWorks2008R2 Administrator 197         Admin-MainServer     1
131         AdventureWorks2008R2 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         AdventureWorks2008R2 Administrator 197         Admin-MainServer     1
131         AdventureWorks2008R2 Administrator 198         Admin-BackupServer   2
106         AdventureWorks2008R2 Operator      210         Operator-MainServer  1