If you’ve already created an association between a principal and a Database Mail profile, but you now want to change whether the profile is the default profile for that user, you can do that with the sysmail_update_principalprofile_sp stored procedure.

When doing this, you need to provide the name or ID of both the user and the profile. This is because you need to specify exactly which profile/principal association you want to update.

Example

Here’s an example to demonstrate:

EXECUTE msdb.dbo.sysmail_update_principalprofile_sp
    @profile_name = 'DB Admin Profile',
    @principal_name = 'Marge',
    @is_default = 1;

This example sets the profile called “DB Admin Profile” to be the default profile for the user called “Marge”.

Setting a Default Public Profile

This example sets the “DB Public Profile” to be the default profile for users in the msdb database:

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

Location of the Stored Procedure

Note that the sysmail_update_principalprofile_sp stored procedure is in the msdb database, and it’s owned by the dbo schema. Therefore, you’ll need to use three part naming if you execute it from outside the msdb database.