Database Email and system objects

Database Email and system objects

Database Mail allows your database applications to send emails to users. These emails can include query results and files from your network.

Database Mail operates with two configuration objects: these allow you to set up the necessary settings for sending emails from your database applications or the SQL Server Agent.

Database Mail Accounts

A Database Mail account stores the details used by Microsoft SQL Server to send emails through an SMTP server. Each account is linked to a specific email server.

Create a New Database Mail Account with SMTP Details

sysmail_add_account_sp  [ @account_name = ] 'account_name',  
    [ @email_address = ] 'email_address' ,  
    [ [ @display_name = ] 'display_name' , ]  
    [ [ @replyto_address = ] 'replyto_address' , ]  
    [ [ @description = ] 'description' , ]  
    [ @mailserver_name = ] 'server_name'  
    [ , [ @mailserver_type = ] 'server_type' ]  
    [ , [ @port = ] port_number ]  
    [ , [ @username = ] 'username' ]  
    [ , [ @password = ] 'password' ]  
    [ , [ @use_default_credentials = ] use_default_credentials ]  
    [ , [ @enable_ssl = ] enable_ssl ]  
    [ , [ @account_id = ] account_id OUTPUT ]

Database Mail Profiles

A Database Mail profile is a group of related Database Mail accounts. Applications that send mail via Database Mail should specify a profile rather than individual accounts.

Profiles also allow database administrators to manage email access. Users need to be a member of the DatabaseMailUserRole to send email via Database Mail.

Profiles can either be public or private.

Create a New Database Mail Profile

sysmail_add_profile_sp [ @profile_name = ] 'profile_name'  
    [ , [ @description = ] 'description' ]  
    [ , [ @profile_id = ] new_profile_id OUTPUT ]

Add an Account to a Database Mail Profile

After creating a Database Account with sysmail_add_account_sp and a Database Profile with sysmail_add_profile_sp, you can use sysmail_add_profileaccount_sp to associate them.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'AdventureWorks Administrator',  
    @account_name = 'Audit Account',  
    @sequence_number = 1 ;

Retrieve Database Mail Configuration

sysmail_help_configure_sp
sysmail_help_principalprofile_sp 
sysmail_help_profile_sp
sysmail_help_profileaccount_sp

Set Database Mail Configuration

The sysmail_configure_sp stored procedure modifies configuration settings for Database Mail at the SQL Server instance level.

Parameter Name	Value	Description
AccountRetryAttempts	1	Number of retry attempts for a mail server
AccountRetryDelay	60	Delay between retry attempts
DatabaseMailExeMinimumLifeTime	600	Minimum process lifetime (seconds)
DefaultAttachmentEncoding	MIME	Default attachment encoding type
LoggingLevel	2	Logging level for Database Mail (normal - 1, extended - 2, verbose - 3)
MaxFileSize	1000000	Maximum file size for attachments
ProhibitedExtensions	exe,dll,vbs,js	Extensions not allowed in email attachments

sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]  
    [ , [ @parameter_value = ] 'parameter_value' ]  
    [ , [ @description = ] 'description' ]

Send an Email Using Database Mail

This procedure sends an email with options for attaching files or query results.

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipient_email' ]  
    [ , [ @copy_recipients = ] 'cc_email' ]  
    [ , [ @blind_copy_recipients = ] 'bcc_email' ]  
    [ , [ @from_address = ] 'sender_email' ]  
    [ , [ @reply_to = ] 'reply_to_email' ]  
    [ , [ @subject = ] 'email_subject' ]  
    [ , [ @body = ] 'email_body' ]  
    [ , [ @body_format = ] 'html' ]  
    [ , [ @importance = ] 'high' ]  
    [ , [ @sensitivity = ] 'normal' ]  
    [ , [ @file_attachments = ] 'file_path' ]  
    [ , [ @query = ] 'sql_query' ]  
    [ , [ @execute_query_database = ] 'database_name' ]  
    [ , [ @attach_query_result_as_file = ] 1 ]  
    [ , [ @query_attachment_filename = ] 'file_name' ]  
    [ , [ @query_result_separator = ] ',' ]  
    [ , [ @exclude_query_output = ] 0 ]  
    [ , [ @append_query_error = ] 1 ]  
    [ , [ @query_no_truncate = ] 1 ]  
    [ , [ @query_result_no_padding = ] 1 ]  
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Enable Database Mail

sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Troubleshooting queries:

select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_event_log;

SELECT [sysmail_server].[account_id],
       [sysmail_account].[name] AS [Account Name],
       [servertype],
       [servername] AS [SMTP Server Address],
       [Port]
FROM [msdb].[dbo].[sysmail_server]
     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
GO
SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'
GO

SELECT * 
FROM msdb.dbo.sysmail_profileaccount pa 
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

SMTP server details for co