In SQL Server, Database Mail uses profiles to send the email rather than accounts directly.

Therefore you must first create a profile, and create an account, then add the account to the profile. You also need to grant a user in the msdb database access to the profile.

To add a Database Mail account to a profile with T-SQL, use the sysmail_add_profileaccount_sp stored procedure in the msdb database.

Example

Here’s an example of adding a Database Mail account to a Database Mail profile using T-SQL.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'DB Admin Profile',  
    @account_name = 'DB Admin',  
    @sequence_number = 1;

In this case I add the account “DB Admin” to the profile “DB Admin Profile”.

This assumes that the profile and account already exist.

You can add multiple accounts to a Database Mail profile. The sequence number determines the order in which accounts are used in the profile. Database Mail starts with the account that has the lowest sequence number. If that account fails, the account with the next highest sequence number is used, and so on.

If more than one account exists with the same sequence number, Database Mail will only use one of those accounts for a given email message.

However, there’s no guarantee that the same account will be used for subsequent messages. For example, if two accounts share a sequence number of 1, you might receive some emails from one account and some from the other account.

Specify Account/Profile IDs

You can alternatively specify the profile 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_profileaccount_sp  
    @profile_id = 1,  
    @account_id = 1,  
    @sequence_number = 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_profileaccount_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.