When you use Database Mail in SQL Server to send emails, you can specify a Database Mail profile to send the mail from.

In some cases you will be required to specify a profile when you send the email. This is the case when the user does not have a default private profile and there is no default public profile for the database.

You can create Database Mail profiles with T-SQL by executing the sysmail_add_profile_sp stored procedure in the msdb database.

Example

Here’s an example of creating a Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'DB Admin Profile',  
    @description = 'Profile for admin emails.';

In this example I specify a profile name of “DB Admin Profile”, and I provide a description.

You also have the option of providing a third argument to return the ID of the new profile (see syntax below).

Note that the sysmail_add_profile_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.

The Syntax

The official syntax goes like this:

sysmail_add_profile_sp [ @profile_name = ] 'profile_name'  
    [ , [ @description = ] 'description' ]  
    [ , [ @profile_id = ] new_profile_id OUTPUT ]

See the Microsoft documentation for a detailed explanation of each argument.

You can also use the sysmail_update_profile_sp stored procedure to update an existing Database Mail profile.

Configuring Database Mail

Creating a profile is just one of several things you need to do before you can send email with that profile.

Once you’ve created the profile, you’ll need to add at least one Database Mail account to that profile (which means that you first need to create an account, if it doesn’t exist already). You’ll also need to grant a user in the msdb database access to the profile.

See How to Send Email in SQL Server for a complete example of configuring Database Mail, then sending an email.