How to Change the Sequence Number of a Database Mail Account within a Profile in SQL Server (T-SQL)

Posted on by Ian

If you’ve already added a Database Mail account to a profile, but you now want to change the sequence number, you can do that with the sysmail_update_profileaccount_sp stored procedure.

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

Example

Here’s an example to demonstrate:

EXECUTE msdb.dbo.sysmail_update_profileaccount_sp  
    @profile_name = 'DB Admin Profile',
    @account_name = 'DB Admin',
    @sequence_number = 2;

This example changes the sequence number for the account called “DB Admin” within the profile called “DB Admin Profile”.

In this case, I’ve changed the sequence number to 2, which means that the “DB Admin” account will only be used if the first account fails.

Using the Profile/Account IDs

In this example I use the profile and account IDs instead of their names:

EXECUTE msdb.dbo.sysmail_update_profileaccount_sp  
    @profile_id = 1,
    @account_id = 1,
    @sequence_number = 3;

Using a Combination of ID and Name

You can alternatively provide the ID of one and the name of the other.

For example:

EXECUTE msdb.dbo.sysmail_update_profileaccount_sp 
    @profile_name = 'DB Admin Profile',
    @account_id = 1,
    @sequence_number = 4;

Or the other way around:

EXECUTE msdb.dbo.sysmail_update_profileaccount_sp
    @profile_id = 1,
    @account_name = 'DB Admin',
    @sequence_number = 1;

How does the Sequence Number Work?

You can add multiple accounts to a Database Mail profile. The sequence number determines the order in which those accounts are used in the profile when sending mail. 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.

I should point out that, according to this Microsoft article, when you send a new email message, Database Mail actually starts with the last account that sent a message successfully, or the account that has the lowest sequence number if no message has yet been sent.

The fact that it starts with the last account that sent a message successfully is not mentioned in the official documentation for sysmail_add_profileaccount_sp or sysmail_update_profileaccount_sp.

Either way, it’s something to be mindful of, and it might save you from tearing your hair out if your DB Mail seems to be ignoring your sequence numbers.

Location of the Stored Procedure

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

How to Return Account and Profile Information

The following articles provide instructions for returning account and profile info.