sysmail_help_principalprofile_sp (Transact-SQL)

Lists information about associations between Database Mail profiles and msdb database principals.

Transact-SQL Syntax Conventions

Syntax

sysmail_help_principalprofile_sp [ {   [ @principal_id = ] principal_id | [ @principal_name = ] 'principal_name' } ]
    [ [ , ] {   [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' } ]

Arguments

• [ @principal_id= ] principal_id
  Is the ID of the msdb database user or 0 for the public role. principal_id is int, with a default of NULL. Either principal_id or principal_name may be specified.

• [ @principal_name= ] 'principal_name'
  Is the name of the msdb database user or 'public' for the public role. principal_name is sysname, with a default of NULL. Either principal_id or principal_name may be specified.

• [ @profile_id= ] profile_id
  Is the ID of the profile for the association to list. profile_id is int, with a default of NULL. Either profile_id or profile_name may be specified.

• [ @profile_name= ] 'profile_name'
  Is the name of the profile for the association to list. profile_name is sysname, with a default of NULL. Either profile_id or profile_name may be specified.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Returns a result set that contains the columns listed in the following table.

Column name	Data type	Description
principal_id	int	The ID of the database user.
principal_name	sysname	The name of the database user.
profile_id	int	The ID number of the Database Mail profile.
profile_name	sysname	The name of the Database Mail profile.
is_default	bit	The flag that states whether the profile is the default profile for the user.

Remarks

If sysmail_help_principalprofile_sp is invoked without parameters, the result set returned lists all of the associations in the instance of SQL Server. Otherwise, the result set contains information for associations that match the provided parameters. For example, the procedure lists all of the associations for a profile when the profile name is provided.

sysmail_help_principalprofile_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

Requires membership in the sysadmin fixed server role.

Examples

A. Listing information for a specific association

The following example shows listing the information for all associations between the AdventureWorks Administrator profile and the ApplicationLogin principal in the msdb database.

EXECUTE msdb.dbo.sysmail_help_principalprofile_sp
    @principal_name = 'danw',
    @profile_name = 'AdventureWorks Administrator' ;

Here is a sample result set, reformatted for line length.

principal_id principal_name     profile_id  profile_name                   is_default
------------ ------------------ ----------- ------------------------------ ----------
5            danw               9           AdventureWorks Administrator   1

B. Listing information for all associations

The following example shows listing the information for all associations in the instance.

EXECUTE msdb.dbo.sysmail_help_principalprofile_sp ;

Here is a sample result set, reformatted for line length.

principal_id principal_name     profile_id  profile_name                   is_default
------------ ------------------ ----------- ------------------------------ ----------
6            terrid             3           Product Update Profile         1
5            danw               9           AdventureWorks Administrator   1

See Also

Reference

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

Other Resources

Database Mail
Select Configuration Task (Database Mail)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	Changed content: Specified users as msdb users and that role is limited to the public role.
14 April 2006	Changed content: Removed nonexistent @database_id and @database_name parameters.