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                 ║
╚═════════════════════╩═══════════════════╝