SQL Server: configure and troubleshoot Database Mail
Queries to inspect, configure, grant, test and troubleshoot Database Mail and its use by SQL Server Agent.
A collection of queries and procedures to configure, grant, test and troubleshoot Database Mail in SQL Server.
Inspect the configuration
select *
from msdb.dbo.sysmail_profile p
join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
join msdb.dbo.sysmail_server s on a.account_id = s.account_id
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
Grant access to Database Mail
Add the user to the DatabaseMailUserRole role of msdb, then grant access to a private profile. Do not forget to also make the profile public if all users must be able to use it.
use msdb
go
-- Add user to the Database Mail role
exec sp_addrolemember 'DatabaseMailUserRole', 'sqlUser'
-- Grants permission for a database user or role
-- to use a private Database Mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'sqlUser',
@profile_name = 'sqlMailProfileName',
@is_default = 0
For a Windows login the role member is written as DOMAIN\app_user.
Check message status
| View | Content |
|---|---|
sysmail_faileditems |
Messages with the failed status |
sysmail_allitems |
All messages processed by Database Mail |
sysmail_unsentitems |
Unsent messages |
sysmail_sentitems |
Sent messages |
sysmail_mailattachments |
E-mail attachments |
sysmail_event_log |
Windows or SQL Server messages returned by Database Mail |
Mail queue status:
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
Send a test mail
USE msdb
GO
EXEC sp_send_dbmail @profile_name='sqlmail',
@recipients='user@example.com',
@subject='Test message',
@body='This is the body of the test message.
Database Mail was received successfully.'
Confirm the account and profile configuration
DECLARE @DatabaseMail VARCHAR(255);
SELECT
ProfileName = smp.name
,AccountName = sma.name
,AccountFromAddress = sma.email_address
,AccountReplyTo = sma.replyto_address
,SMTPServer = sms.servername
,SMTPPort = sms.port
FROM msdb.dbo.sysmail_account sma
INNER JOIN msdb.dbo.sysmail_profileaccount smpa ON sma.account_id = smpa.account_id
INNER JOIN msdb.dbo.sysmail_profile smp ON smpa.profile_id = smp.profile_id
INNER JOIN msdb.dbo.sysmail_server sms ON sma.account_id = sms.account_id;
Confirm SQL Server Agent uses Database Mail
DECLARE @DatabaseMail VARCHAR(255);
DECLARE @res TABLE
(
Value VARCHAR(255)
, Data VARCHAR(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail';
INSERT INTO @res
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile';
IF (
SELECT COUNT(*)
FROM @res r
WHERE r.Value = 'UseDatabaseMail' AND r.Data = 1
) = 1 AND
(
SELECT COUNT(*)
FROM @res r
WHERE r.Value = 'DatabaseMailProfile' AND r.Data IS NOT NULL
) = 1
SET @DatabaseMail = 'Configured'
ELSE
SET @DatabaseMail = 'Not Configured';
select @DatabaseMail
Alternative that returns the raw registry values:
DECLARE @res TABLE
(
Value VARCHAR(255)
, Data VARCHAR(255)
);
INSERT INTO @res
EXEC master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'Software\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'DatabaseMailProfile';
INSERT INTO @res
EXEC sys.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE'
, @key = N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
, @value_name = N'UseDatabaseMail'
SELECT *
FROM @res;
Full setup script
Enables Database Mail, creates a profile and an SMTP account, links them, and creates an Agent operator.
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
sp_configure 'show advanced options',0
go
reconfigure
go
-- Profile
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'sqlmail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'sqlmail',
@description = '';
END
-- Account
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'SQL Server Mail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL Server Mail',
@email_address = 'user@example.com',
@display_name = 'sqlserver01',
@replyto_address = 'user@example.com',
@description = '',
@mailserver_name = 'smtp.example.com',
@mailserver_type = 'SMTP',
@port = '25',
@username = NULL,
@password = NULL,
@use_default_credentials = 0,
@enable_ssl = 0;
END
-- Associate the account to the profile
IF NOT EXISTS(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
WHERE p.name = 'sqlmail'
AND a.name = 'SQL Server Mail')
BEGIN
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'sqlmail',
@account_name = 'SQL Server Mail',
@sequence_number = 1;
END
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_operator',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'user@example.com',
@category_name=N'[Uncategorized]'
GO
Troubleshooting
For the error "Profile name is not valid" (msg 14607), see this article.