主页 / dba / 问题 / 199827
Accepted
S3S
Asked: 2018-03-10 08:51:17 +0800 CST

查询 SQL Server 警报系统邮件会话设置

  • 772

我正在寻找一种方法来查询代理 > 警报系统 > 邮件会话下的邮件会话设置。类似于 sp_MSgetalertinfo 返回有关故障安全操作员的信息的方式，我想确定是否选中了启用邮件配置文件，以及邮件系统和邮件配置文件设置的内容。我了解这些可能仅包含在注册表设置中。

我想查询这些的原因是因为我们启用了它们......但现在它们已经两次被某人禁​​用，在某个时间点，我只是抓住了它，因为我得到了可怕的“[476]没有为代理通知启用数据库邮件。无法将电子邮件发送到“代理日志中的错误。这是支持人员可以完全访问的第三方数据库。我不确定他们是否无意（或故意）禁用了它，或者它是否可以被其他系统禁用意味着不是由用户完成的。

为了不让这成为 XY 问题，我愿意接受有关如何使用服务器审核规范审核这些更改的建议。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    我使用此代码来验证数据库邮件配置文件配置：

    /* 
    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;

    输出看起来像：

    ╔══════════════╦═════════════╦═════════════╦══════ ═════════════════╦════════════════════════╦═══════ lu
║ ServerName ║ ProfileName ║ AccountName ║ AccountFromAddress ║ AccountReplyTo ║ SMTPServer ║ SMTPPort ║ AgentDatabaseMail ║
╠══════════════╬═════════════╬═════════════╬══════ ═════════════════╬════════════════════════╬═══════ ═════════════════╬══════════╬═════════════════
║ 服务器名 ║ DBA ║ SMTP ║ [email protected] ║ [email protected] ║ mailserver.company.com ║ 25 ║ 已配置 ║
╚══════════════╩═════════════╩═════════════╩══════ ═════════════════╩════════════════════════╩═══════ lu

    SQL Server Agent 使用的各种参数存储在注册表中。我使用此代码来获取这些详细信息：

    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;

    输出：

    ╔═════════════════════╦════════════════════
║ 价值 ║ 数据 ║
╠═════════════════════╬═════════════════════
║ DatabaseMailProfile ║ MV2012MailProfile ║
║ 使用数据库邮件 ║ 1 ║
╚═════════════════════╩══════════════════╝═════════════
    • 1

相关问题