sysmail_help_profileaccount_sp (Transact-SQL)
Applies to: SQL Server
Lists the accounts associated with one or more Database Mail profiles.
Transact-SQL syntax conventions
sysmail_help_profileaccount_sp
{ [ @profile_id = ] profile_id
| [ @profile_name = ] 'profile_name' }
[ , { [ @account_id = ] account_id
| [ @account_name = ] 'account_name' } ]
[ ; ]
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.
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.
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.
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.
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. |
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
.
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may be overridden during a SQL Server upgrade.
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
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
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