sysmail_help_profileaccount_sp (Transact-SQL)

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

Transact-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.

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 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

See Also

Reference

Database Mail and SQL Mail Stored Procedures (Transact-SQL)

Other Resources

Database Mail
Database Mail Accounts
Database Mail Profiles
Select Configuration Task (Database Mail)

Help and Information

Getting SQL Server 2005 Assistance