Database Mail Profiles

A Database Mail profile is an ordered collection of related Database Mail accounts. To send mail you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile. Profiles allow database administrators to reconfigure stored procedures and database applications that use e-mail without changing the application code. For example, a profile can be configured with one set of e-mail accounts during application development and testing, and then be updated with a different set of accounts when the application moves to production. The application uses the same profile name, but sends e-mail using a different list of e-mail servers.

Note

Because Database Mail does not use the Messaging Application Programming Interface (MAPI), Database Mail profiles are not MAPI mail profiles. Database Mail does not use SQL Mail profiles.

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.

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. For each private profile, Database Mail maintains a list of users that are permitted to send e-mail using that profile. Public profiles are available to users or roles in the msdb database who are also members of the DatabaseMailUserRole.

By default, a profile is private, and no users are granted access to the profile. To make the profile public, grant access to the user 'public' or the user id 0. For more information on grant access to profiles, see sysmail_add_principalprofile_sp (Transact-SQL).

A profile may be a default profile. In this case, users can send e-mail using the profile without explicitly specifying the profile. If the user sending the e-mail message has a default private profile, Database Mail uses that profile. If the user has no default private profile, sp_send_dbmail uses the default public profile for the database. If there is no default private profile for the user and no default public profile for the database, sp_send_dbmail returns an error. In all cases, a user must be a member of the DatabaseMailUserRole to send Database Mail.