[ @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. A principal_id of 0 makes this profile a public profile, granting access to all principals in the database.

[ @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 must be specified. A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.

[ @profile_id = ] profile_id

The id of the profile for the association. 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. profile_name is sysname, with no default. Either profile_id or profile_name must be specified.

[ @is_default = ] is_default

Specifies whether this profile is the default profile for the principal. A principal must have exactly one default profile. is_default is bit, with no default.

0 (success) or 1 (failure)

To make a profile public, specify a @principal_id of 0 or a @principal_name of public. A public profile is available to all users in the msdb database, though users must also be a member of DatabaseMailUserRole to execute sp_send_dbmail.

A database user may only have one default profile. When @is_default is '1' and the user is already associated with one or more profiles, the specified profile becomes the default profile for the user. The profile that was previously the default profile is still associated with the user, but is no longer the default profile.

When @is_default is '0' and no other association exists, the stored procedure returns an error.

The stored procedure sysmail_add_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.

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

A. Creating an association, setting the default profile

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

B. Making a profile the default public profile

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'AdventureWorks Public Profile',
    @is_default = 1 ;

Reference

Other Resources

Help and Information