When you use the sp_send_dbmail stored procedure to send emails in SQL Server, you would typically include the @profile_name argument to specify which profile to use.

However, if you omit this argument, sp_send_dbmail will use the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail will use the default public profile for the msdb database.

If none of these are configured, you’ll get the following error:

Msg 14636, Level 16, State 1, Procedure msdb.dbo.sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.

If you’re getting this error, you basically have a three options:

• Specify which profile to use, by including the @profile_name argument when calling the sp_send_dbmail procedure.
• Create a default private profile for the current user.
• Create a public profile for the msdb database.

This article provides an example of the third option: create a public profile for the msdb database.

Example

Here’s an example that goes through the process of creating a Database Mail profile, creating a Database Mail account, adding the account to the profile, then granting public access to that profile.

-- Create a Database Mail profile  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'DB Public Profile',  
    @description = 'Public Profile for emails.'; 

-- Create a Database Mail account  
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_id = 1,
    @account_name = 'DB Public',  
    @description = 'Public account for emails.',  
    @email_address = '[email protected]',  
    @replyto_address = '[email protected]',  
    @display_name = 'DB Public Mailer',  
    @mailserver_name = 'smtp.example',
    @port = 587;

-- Add the account to the profile  
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'DB Public Profile',  
    @account_name = 'DB Public',  
    @sequence_number = 1;

-- Grant the public principal access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DB Public Profile',
    @principal_name = 'public',
    @is_default = 1;

The last line is actually the part that makes it the default public profile.

By using @is_default = 1, I’m setting this profile to be the default public profile. If I’d used @is_default = 0 instead, it would not be the default public profile.

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 (although users must also be a member of DatabaseMailUserRole to execute sp_send_dbmail).

Note that there can only be one default public profile.

Update an Existing Profile

If you’ve already got a public profile, but it’s not the default public profile, you can use sysmail_update_principalprofile_sp to change that.

Example:

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