To send email using Database Mail in SQL Server, a user must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.

To grant a user access to a Database Mail profile with T-SQL, use the sysmail_add_principalprofile_sp stored procedure in the msdb database.

Example

Here’s an example to demonstrate.

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

In this case I grant the user “Marge” access to the profile “DB Admin Profile”.

This assumes that the profile and user already exist.

In this case I also specifed @is_default = 1, which makes this the default profile for the user.

Specify User/Profile IDs

You can alternatively specify the user ID and/or the account ID instead of their names.

Therefore, we could change the previous example to look something like this:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_id = 1,
    @principal_id = 25,
    @is_default = 1;

This obviously assumes that those are the IDs for the accounts in question.

Note that either the name or the ID must be provided.

Also, when providing the IDs, they are provided as int.

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

Public Profiles

You can also make a profile the default public profile for users in the msdb database.

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

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.

Configuring Database Mail

The above steps assume that you’ve already created a Database Mail profile and added at least one Database Mail account to that profile.

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