Query SQL Server Alert System Mail Session Settings

Question

I'm looking for a way to query the Mail session settings under Agent> Alert System> Mail Session. Similar to how sp_MSgetalertinfo returns information about the Fail-safe operator, I'd like to determine if the Enable mail profile is checked, and what the Mail system and Mail profile are set to. I understand these may only be contained in registry settings.

The reason why I want to query for these is because we have them enabled... but twice now they have been disabled by someone, at some point in time, and I've only caught it because I got the dreaded "[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to" error in the agent log. This is a third part DB which support has full access to. I'm not sure if they accidentally (or purposely) disabled it, or if it could be disabled by some other system means that isn't done by a user.

In order to not make this an XY question, I'm open to suggestions on how to audit these changes with a server audit specification.

Answer 1

I use this code to validate Database Mail profile configuration:

/* 
 Confirm the Database Mail account and profile is configured correctly 
*/ 
DECLARE @ProfileName varchar(255); 
DECLARE @AccountName varchar(255); 
DECLARE @AccountFromAddress varchar(255); 
DECLARE @AccountReplyTo varchar(255); 
DECLARE @SMTPServer varchar(255); 
DECLARE @SMTPPort varchar(255); 
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 is configured to use Database Mail correctly 
*/ 
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_BIG(*) 
 FROM @res r 
 WHERE r.[Value] = 'UseDatabaseMail' AND r.[Data] = 1 
 ) = 1 AND 
 ( 
 SELECT COUNT_BIG(*) 
 FROM @res r 
 WHERE r.[Value] = 'DatabaseMailProfile' AND r.[Data] IS NOT NULL 
 ) = 1 
 SET @DatabaseMail = 'Configured' 
ELSE 
 SET @DatabaseMail = 'Not Configured'; 
 
/* 
 Combine results into a single row 
*/ 
SELECT 
 ServerName = @@SERVERNAME 
 , ProfileName = @ProfileName 
 , AccountName = @AccountName 
 , AccountFromAddress = @AccountFromAddress 
 , AccountReplyTo = @AccountReplyTo 
 , SMTPServer = @SMTPServer 
 , SMTPPort = @SMTPPort 
 , AgentDatabaseMail = @DatabaseMail; 

The output looks like:

╔══════════════╦═════════════╦═════════════╦═══════════════════════╦════════════════════════╦════════════════════════╦══════════╦═══════════════════╗
║ ServerName ║ ProfileName ║ AccountName ║ AccountFromAddress ║ AccountReplyTo ║ SMTPServer ║ SMTPPort ║ AgentDatabaseMail ║
╠══════════════╬═════════════╬═════════════╬═══════════════════════╬════════════════════════╬════════════════════════╬══════════╬═══════════════════╣
║ servername ║ DBA ║ SMTP ║ [email protected] ║ [email protected] ║ mailserver.company.com ║ 25 ║ Configured ║
╚══════════════╩═════════════╩═════════════╩═══════════════════════╩════════════════════════╩════════════════════════╩══════════╩═══════════════════╝

Various parameters used by SQL Server Agent are stored in the registry. I use this code to get those details:

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 r.[Value]
 , r.[Data]
FROM @res r;

The output:

╔═════════════════════╦═══════════════════╗
║ Value ║ Data ║
╠═════════════════════╬═══════════════════╣
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ UseDatabaseMail ║ 1 ║
╚═════════════════════╩═══════════════════╝

• Sir Swears-a-lot: Great post. Still valid :)