SQL Server 2008 DDL 觸發器與登入觸發器完整實作：稽核 DDL 操作與限制登入時間

前言

SQL Server 提供三類觸發器：DML、DDL、登入。本文專注 DDL 觸發器 (回應 CREATE/ALTER/DROP 等結構定義語句) 與 登入觸發器 (回應 LOGON 事件)，示範稽核資料庫/伺服器層級 DDL 變更、阻擋建立登入帳號、限制特定帳號登入時間。

範例一：資料庫層級 DDL 觸發器 - 稽核索引變更

USE master; GO
CREATE TABLE dbo.ChangeAttempt (
    EventData xml NOT NULL,
    AttemptDate datetime NOT NULL DEFAULT GETDATE(),
    DBUser char(50) NOT NULL
); GO

CREATE TRIGGER db_trg_RestrictINDEXChanges
ON DATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;
    INSERT dbo.ChangeAttempt (EventData, DBUser)
    VALUES (EVENTDATA(), USER);
END; GO

-- 測試
CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON dbo.ChangeAttempt(DBUser); GO
SELECT EventData FROM dbo.ChangeAttempt; GO

範例二：伺服器層級 DDL 觸發器 - 禁止建立登入

USE master; GO
CREATE TRIGGER srv_trg_RestrictNewLogins
ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
    PRINT 'No login creations without DBA involvement.';
    ROLLBACK;
END; GO

-- 測試 → 會被阻擋並回滾
CREATE LOGIN johny WITH PASSWORD = '123456'; GO

-- 移除觸發器
DROP TRIGGER srv_trg_RestrictNewLogins ON ALL SERVER; GO

⚠ 警告：伺服器層級 DDL 觸發器設定不當可能導致無法管理伺服器，請極度謹慎測試後再部署生產環境。

範例三：登入觸發器 - 限制特定帳號僅能 07:00-17:00 登入

-- 建立測試帳號與稽核表
CREATE LOGIN nightworker WITH PASSWORD = '123b3b4'; GO
CREATE DATABASE ExampleAuditDB; GO
USE ExampleAuditDB; GO
CREATE TABLE dbo.RestrictedLogonAttempt (
    LoginNM sysname NOT NULL,
    AttemptDT datetime NOT NULL
); GO

-- 建立登入觸發器
USE master; GO
CREATE TRIGGER trg_logon_attempt
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'nightworker'
       AND DATEPART(hour, GETDATE()) BETWEEN 7 AND 17
    BEGIN
        ROLLBACK; -- 拒絕登入
        INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt (LoginNM, AttemptDT)
        VALUES (ORIGINAL_LOGIN(), GETDATE());
    END
END; GO

-- 查看稽核記錄
USE ExampleAuditDB; GO
SELECT * FROM dbo.RestrictedLogonAttempt; GO

-- 清理
DROP TRIGGER trg_logon_attempt ON ALL SERVER; GO
DROP DATABASE ExampleAuditDB; GO

小結

1. DDL 觸發器適合**稽核結構變更**、**強制命名規範**、**阻擋高風險操作**。
2. 登入觸發器可實作**時間限制**、**IP 限制**、**並發連線控制**等安全策略。
3. 兩者皆屬伺服器/資料庫層級物件，部署前務必在測試環境完整驗證。