sysmail_update_principalprofile_sp (Transact-SQL)

Updates the information for an association between an msdb principal and a profile.

Transact-SQL Syntax Conventions

Syntax

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

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 must 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
  The id of the profile for the association to change. profile_id is int, with a default of NULL. Either profile_id or profile_name must be specified.

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

• [ @is_default= ] 'is_default'
  Is whether this profile is the default profile for the database user. A database user may only have one default profile. is_default is bit, with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

This stored procedure changes whether the profile specified is the default profile for the database user. A database user may only have one default private profile.

When the principal name for the association is public or the principal id for the association is 0, this stored procedure changes the public profile. There can only be one default public profile.

When @is_default is '1' and the principal is associated with more than one profile, the specified profile becomes the default profile for the principal. The profile that was previously the default profile is still associated with the principal, but is no longer the default profile.

The stored procedure sysmail_update_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

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

Examples

A. Setting a profile to be the default public profile for a database

The following example sets the profile General Use Profile to be the default public profile for users in the msdb database.

EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'General Use Profile',
    @is_default = '1';

B. Setting a profile to be the default private profile for a user

The following example sets the profile AdventureWorks Administrator to be the default profile for the principal ApplicationUser in the msdb database. The profile must already be associated with the principal. The profile that was previously the default profile is still associated with the principal, but is no longer the default profile.

EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks Administrator',
    @is_default = '1' ;

See Also

Reference

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

Other Resources

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

Help and Information

Getting SQL Server 2005 Assistance

Change History