Database Mail Configuration Objects

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Database Mail has two configuration objects: The database configuration objects provide a way for you to configure the settings that Database mail should use when sending an email from your database application or SQL Server Agent.

The illustration shows two profiles, three accounts, and three users. User 1 has access to Profile 1, which uses Account 1 and Account 2. User 3 has access to Profile 2, which uses Account 2 and Account 3. User 2 has access to both Profile 1 and Profile 2.

A Database Mail profile is an ordered collection of related Database Mail accounts. Applications that send e-mail using Database Mail specify profiles, instead of using accounts directly. Separating information about the individual e-mail servers from the objects that the application uses improves flexibility and reliability: profiles provide automatic failover, so that if one e-mail server is unresponsive, Database Mail can automatically send mail to another e-mail server. Database administrators can add, remove, or reconfigure accounts without requiring changes to application code or job steps.

Profiles also help database administrators control access to e-mail. Membership in the DatabaseMailUserRole is required to send Database Mail. Profiles provide additional flexibility for administrators to control who sends mail and which accounts are used.

A profile may be public or private.

Public profiles are available for all members of the DatabaseMailUserRole database role in the msdb database. They allow all members of the DatabaseMailUserRole role to send e-mail using the profile.

Private profiles are defined for security principals in the msdb database. They allow only specified database users, roles, and members of the sysadmin fixed server role to send e-mail using the profile. By default, a profile is private, and allows access only to members of the sysadmin fixed server role. To use a private profile, sysadmin must grant users permission to use the profile. Additionally, EXECUTE permission on the sp_send_dbmail stored procedure is only granted to members of the DatabaseMailUserRole. A system administrator must add the user to the DatabaseMailUserRole database role for the user to send e-mail messages.

Profiles improve reliability in cases where an e-mail server becomes unreachable or unable to process messages. Each account in the profile has a sequence number. The sequence number determines the order in which Database Mail uses accounts in the profile. For a new e-mail message, Database Mail uses the last account that sent a message successfully, or the account that has the lowest sequence number if no message has yet been sent. Should that account fail, Database Mail uses the account with the next highest sequence number, and so on until either Database Mail sends the message successfully, or the account with the highest sequence number fails. If the account with the highest sequence number fails, the Database Mail pauses attempts to send the mail for the amount of time configured in the AccountRetryDelay parameter of sysmail_configure_sp, then starts the process of attempting to send the mail again, starting with the lowest sequence number. Use the AccountRetryAttempts parameter of sysmail_configure_sp, to configure the number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.

If more than one account exists with the same sequence number, Database Mail only uses one of those accounts for a given e-mail message. In this case, Database Mail makes no guarantees as to which of the accounts is used for that sequence number or that the same account is used from message to message.