2

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.

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(*)  
        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';  

/*  
    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        ║ sqlserver@company.com ║ dbasupport@company.com ║ 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 *
FROM @res;

The output:

╔═════════════════════╦═══════════════════╗
║        Value        ║       Data        ║
╠═════════════════════╬═══════════════════╣
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ UseDatabaseMail     ║ 1                 ║
╚═════════════════════╩═══════════════════╝
  • Max, i can not thank you enough sir. I searched high and low for something like this! – scsimon Mar 9 '18 at 18:53
  • 1
    My pleasure. It's painful that there isn't an easy, documented way to do this. – Max Vernon Mar 9 '18 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.