多人共用資料庫 (DBA、開發、運維) 時，未經管控的 DDL 操作 (建表、改 SP、刪索引) 極易造成混亂與事故。本文實作基於 DDL Trigger + EVENTDATA() 的自動稽核系統，記錄變更細節並即時郵件通知。

一、架構設計

1. 稽核表 msdb.dbo.DatabaseLog：集中儲存所有資料庫的 DDL 事件
2. 資料庫級 DDL Trigger：捕獲 DDL_DATABASE_LEVEL_EVENTS，解析 XML 事件資料寫入稽核表並發信
3. Database Mail：預先設定郵件設定檔 (Profile) 與收件人

二、建立中央稽核表

USE msdb;
GO

CREATE TABLE dbo.DatabaseLog
(
    DatabaseLogID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    PostTime      DATETIME      NOT NULL,
    DatabaseUser  sysname       NOT NULL,
    LoginName     sysname       NULL,
    ClientHost    sysname       NULL,
    EventType     sysname       NOT NULL,  -- CREATE_TABLE, ALTER_PROCEDURE...
    SchemaName    sysname       NULL,
    ObjectName    sysname       NULL,
    TSQLCommand   NVARCHAR(MAX) NOT NULL,
    XmlEvent      XML           NOT NULL
);
GO

三、建立 DDL Trigger (以資料庫 MyAssistant 為例)

USE MyAssistant;
GO

CREATE TRIGGER DTG_DatabaseDdlTriggerLog
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @data XML = EVENTDATA();
    DECLARE @eventType sysname = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    DECLARE @schema    sysname = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @object    sysname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
    DECLARE @tsql      NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
    DECLARE @loginName NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(MAX)');

    -- 寫入稽核表
    INSERT INTO msdb.dbo.DatabaseLog
    (PostTime, DatabaseUser, LoginName, ClientHost, EventType, SchemaName, ObjectName, TSQLCommand, XmlEvent)
    VALUES
    (GETDATE(), CURRENT_USER, @loginName, HOST_NAME(), @eventType, @schema, @object, @tsql, @data);

    -- 組裝 HTML 郵件內容 (僅含最新一筆)
    DECLARE @tableHTML NVARCHAR(MAX) = 
        N'
DDL Event Alert
' +
        N'' +
        N'' +
        CAST((
            SELECT 
                td = CONVERT(VARCHAR(23), PostTime, 121), '',
                td = DatabaseUser, '',
                td = LoginName, '',
                td = ClientHost, '',
                td = EventType, '',
                td = SchemaName, '',
                td = ObjectName, '',
                td = TSQLCommand, ''
            FROM msdb.dbo.DatabaseLog
            WHERE DatabaseLogID = (SELECT MAX(DatabaseLogID) FROM msdb.dbo.DatabaseLog)
            FOR XML PATH('tr'), TYPE
        ) AS NVARCHAR(MAX)) +
        N'
Time
DB User
Login
Client
Event
Schema
Object
TSQL
';

    -- 發送郵件 (需預先設定 Database Mail Profile)
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DataBase_DDL_Event',  -- 郵件設定檔名稱
        @recipients   = 'dba-team@company.com', -- 收件人
        @subject      = 'DDL Event - Database: MyAssistant',
        @body         = @tableHTML,
        @body_format  = 'HTML';
END;
GO

四、測試驗證

1. 使用測試帳號登入 SSMS
2. 執行 DDL：DROP TABLE TestTable; 或 ALTER PROCEDURE usp_Test ...
3. 查詢稽核表：SELECT TOP 10 * FROM msdb.dbo.DatabaseLog ORDER BY PostTime DESC;
4. 檢查收件箱是否收到 HTML 格式告知郵件

五、EVENTDATA() 常用 XPath 路徑速查

六、進階擴充建議

• 伺服器級 Trigger：ON ALL SERVER FOR DDL_SERVER_LEVEL_EVENTS 監控 CREATE DATABASE, ALTER LOGIN 等實例級事件
• 過濾噪音：排除 UPDATE STATISTICS, CREATE STATISTICS 等高頻自動事件
• 異步處理：Trigger 中寫入 Service Broker 隊列，由獨立啟動程序發信，避免郵件發送失敗阻塞 DDL
• 權限管控：稽核表僅授 db_owner / 稽核角色讀取，防竄改
• 保留策略：定期封存/刪除超過 1 年的稽核記錄

七、注意事項

1. DDL Trigger 在交易內執行，若 Trigger 失敗會導致原 DDL 回滾 → Trigger 邏輯必須極度健壯，郵件發送失敗不應拋出錯誤 (可用 TRY...CATCH 吞掉)
2. 大量 DDL 部署時可暫時停用 Trigger：DISABLE TRIGGER DTG_DatabaseDdlTriggerLog ON DATABASE;
3. 跨資料庫寫入 msdb 需確保 Trigger 擁有者有 msdb 寫入權限 (通常 dbo 擁有者即可)