LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon