[分享]使用SQLServer Audit来监控触发器的启用、禁用情况

[發糞塗牆]

截图请看：http://blog.csdn.net/dba_huangzj/article/details/8229578

由于给小李子那个版主骗了，没啥可用分，所以就不散分了.....

使用情景：
有时候会发现在触发器中的业务逻辑没有执行，可能是因为触发器的逻辑错误所引起的。但是有时候却是因为一些触发器被禁用了。

由于SQLServer默认不跟踪触发器的启用还是禁用。且禁用触发器的命令（Disable Trigger）不在默认跟踪里面捕获。但是可以在服务器级别的跟踪（不是使用Profiler）获取这些信息，捕获SQL:StmtCompleted并在TextData列筛选，但是对于负载较重的系统，这样会有比较大的影响。

如果你的是企业版，可以考虑使用一个新特性：SQL Server Audit。可以使用DATABASE AUDIT SPECIFICATION来捕获这些事件。


使用步骤：


第一步，使用以下语句先创建服务器级别监控：

USE master;  
  
GO  
  
CREATE SERVER AUDIT ServerAudit  
  
  TO FILE (FILEPATH = 'E:\temp\', MAXSIZE = 1 GB)--注意更改文件路径   
  
  WITH (ON_FAILURE = CONTINUE);  
  
GO  
  
ALTER SERVER AUDIT ServerAudit  
  
  WITH (STATE = ON);

注意，路径需要修改，temp文件夹也要实现开启。


第二步，创建数据库级别监控:


对于本文，我们关注SCHEMA_OBJECT_CHANGE_GROUP，以AdventureWorks为例子：

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

第三步，创建一个示例表，然后创建一个示例触发器：

CREATE TABLE dbo.splunge ( id INT ) ;
GO
CREATE TRIGGER dbo.splunge_trigger ON dbo.splunge
    FOR INSERT
AS
    BEGIN
        SELECT  1 ;
    END
GO

第四步，可以使用以下脚本来检查：

SELECT  event_time ,  
        succeeded ,  
        server_principal_name ,  
        [object_name] ,  
        [statement]  
FROM    sys.fn_get_audit_file('E:\Temp\ServerAudit*', NULL, NULL)  
WHERE   database_name = 'AdventureWorks' ;

结果如下，可以看到捕获了创建的记录：


现在来禁用这个触发器，然后再查询一下：

DISABLE TRIGGER dbo.splunge_trigger ON dbo.splunge;
GO
SELECT  event_time ,
        succeeded ,
        server_principal_name ,
        [object_name] ,
        [statement]
FROM    sys.fn_get_audit_file('E:\Temp\ServerAudit*', NULL, NULL)
WHERE   database_name = 'AdventureWorks' ;

如果你重新启用这个触发器，你会再看到另外一行，如果觉得返回数据太多，可以在where语句中添加筛选：

AND [statement] LIKE '%disable%trigger%'

但是有时候会存在误报，比如，在你的代码里面可能存在同样的信息。但是筛选数据对性能和检查问题总是有好处的。



小结：
捕获事件有很多种方式。此功能仅限2008企业版使用。

[Mr_zhangp]

顶！！！！！！！！！！！！！！！！！！~

[开着拖拉机泡妞]

蛋贴 坚决抵制

[newtee]

[--小F--]

又是2008.

[發糞塗牆]

引用 4 楼 fredrickhu 的回复:

又是2008.

工作所需

[开着拖拉机泡妞]

引用 4 楼 fredrickhu 的回复:

又是2008.

关你屁事

[好帅的一条鱼]

顶了在看

[黄_瓜]

为什么不直接访问sys.triggers 用Audit就问了监听触发器是否禁用太大材小用了吧

[發糞塗牆]

引用 8 楼 Beirut 的回复:

为什么不直接访问sys.triggers 用Audit就问了监听触发器是否禁用太大材小用了吧

因为sys.triggers只能看到当前状态，看不到历史状态。另外我也是翻译的。不是我自己写的，还没到这个水平

[SQL77]

学习...MARK

[lzz8687]

学习下 啊 是

[钝忆思梦]

先留个印。

[夜予]

引用 10 楼 SQL77 的回复:

学习...MARK

+

[纽约的风景看腻了]

我本来要用audit的，但因为没客户端主机名所以没用，是不是到connecttion表里可以查到？另外2005有audit吗？有没有用sql语句启动跟踪的方法？最好是把跟踪保存到表，如果只能保存到文件的话，有能启动的sql语句吗，不只是定义，我想从自己程序启动而不是sql的客户端。

[青_灯]

先留个印大大