Skip to content

Latest commit

 

History

History
143 lines (101 loc) · 5.21 KB

sysmail-help-profileaccount-sp-transact-sql.md

File metadata and controls

143 lines (101 loc) · 5.21 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sysmail_help_profileaccount_sp (Transact-SQL)
Lists the accounts associated with one or more Database Mail profiles.
markingmyname
maghan
randolphwest
05/30/2023
sql
system-objects
reference
sysmail_help_profileaccount_sp_TSQL
sysmail_help_profileaccount_sp
sysmail_help_profileaccount_sp
TSQL

sysmail_help_profileaccount_sp (Transact-SQL)

[!INCLUDE SQL Server]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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

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'

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

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'

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 set

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 isn't msdb.

Permissions

[!INCLUDE msdb-execute-permissions]

Examples

A. List 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.

EXEC 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. List 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.

EXEC 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. List the accounts for all profiles

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

EXEC 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

Related content