[SQL] 標準報表-結構描述變更紀錄

參加研討會時，老師說明透過 [結構描述變更紀錄報表]，在沒有事先建立 DDL Trigger、開啟 Audit 的情況下，抓到刪除重建 Table 的兇手，才注意到有這張標準報表，原以為是透過 Extend Event 來產生的，實際測試時發現是抓取 Default SQL Trace 內的資料

• 利用 T-SQL 語法檢查 SQL Server 是否有 DDL 操作，並發信通知 DBA

利用 SQL Profile 側錄報表產生時的 TSQL 語法並自行改寫一下

CREATE PROCEDURE uspDDLCheck
AS
  BEGIN
      DECLARE @curr_tracefilename varchar(500) ; 
      DECLARE @base_tracefilename varchar(500) ; 
      DECLARE @indx int ;
 
      SELECT @curr_tracefilename = [path] FROM sys.traces WHERE is_default = 1 ; 
      SET @curr_tracefilename = REVERSE(@curr_tracefilename);
      SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
      SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
      SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;  
 
      IF EXISTS
        (
            SELECT 1
            FROM fn_trace_gettable( @base_tracefilename, default )
            WHERE DatabaseID = db_id()
                  AND EventClass in (46,47,164)
                  -- 46 為 CREATE 
                  -- 47 為 DROP
                  -- 164 為 ALTER
                  AND EventSubclass = 0
                  AND ObjectType NOT IN (17747,21587)
                  -- 17747 為 Security Event
                  -- 21587 為統計資訊
                  AND ObjectID  > 100
                  -- ObjectID 100 以下為系統物件                  
                  AND DatabaseName NOT IN ('tempdb')
                  -- 可能會在 TempDB 內操作 Temp Table，不列入追蹤                  
                  AND StartTime > DATEADD(HH, -24, GETDATE())
                  -- 判斷昨天有沒有發生
                  AND CHARINDEX('SQLAgent' , ApplicationName) = 0
                  -- 排程工作，EX：維護索引，不列入追蹤                  
        )
        BEGIN
            EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'Database Mail Profile' ,
                @recipients='DBA@gmail.com' ,
                @subject = 'DDL Alert',
                @body = 'DDL is triggered' ,
                @body_format = 'TEXT'
        END
  END

• 延伸閱讀
• [SQL] 標準報表 - 磁碟使用量
• 參考資料
• ObjectType 追蹤事件資料行
• The default trace in SQL Server – the power of performance and security auditing - 有整理好的 ObjectType 可以使用