SQL – View SQL Server Database Mail Log (All / Successful / Failed)

SQL Server keeps a log of mail sent via Database Mail and the sp_send_dbmail Stored Procedure.

I find these code snippets useful to view mail history including sent and failed items. You can view address, subject & body, file attachment(s), status, sent date, etc.

All Messages – regardless of status and deliverability
SELECT top 50 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC
Sent Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC
Failed Items
SELECT TOP 50 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

SQL Server Allow users to access sp_send_dbmail to send email

After setting up an email account / profile in SQL server (https://robbamforth.wordpress.com/2008/10/21/sql-server-2005-database-mail/) you must give each user account permission to access the sp_send_dbmail procedure.

This is done in the following way:

When setting up a new user, select the User Mapping option from the left hand side of the screen.
Scroll down to the MSDB database and tick the box next to it.
Under Database role Membership for:MSDB select DatabaseMailUserRole.
Complete the new user set up as usual.

This user now has permission to access procedures using the sp_send_dbmail command, such as sending database mail:

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = EMAIL_PROFILE’,

    @recipients = test@test.som’,

    @subject = ‘This is a test email’,

    @body = ‘Sample Message’,    

    @body_format = ‘HTML’;