五餅二魚工作室

在前一篇「是誰偷改登入帳號的密碼 ?」的時候有提到，因此要考慮配合舊版本的 SQL Server 都可以使用，因此可以採用 SQL Trace 來做處理，但如果您所使用的是 SQL Server 2005 以上的版本，也可以用 DDL Trigger 來做紀錄。這部份可以參考另外一篇「不要偷改我的資料庫啦 !」，當時的案例是用來紀錄資料結構的改變。

當我們要開始使用 DDL Trigger 的時候，我們可以先在 master 資料庫裡面建立一個資料表來存放異動紀錄。

   1: USE [master]

   2: GO

   3:  

   4: CREATE TABLE [dbo].[ChangeLogin_Log](

   5:     [ActionTime] [datetime] NULL DEFAULT (getdate()),

   6:     [Username] [nvarchar](100) NULL,

   7:     [EventType] [nvarchar](100) NULL,

   8:     [Data] [xml] NULL

   9: ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  10: GO

接著建立我們所要使用的 DDL Trigger，用來紀錄當建立、修改、刪除帳號的時候

   1: CREATE TRIGGER [ChangeLogin] ON ALL SERVER 

   2: AFTER CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN

   3: AS

   4:     DECLARE @data xml = eventdata();

   5:     BEGIN 

   6:         INSERT INTO ChangeLogin_Log(Username,EventType,Data )

   7:         VALUES

   8:             ( CURRENT_USER,

   9:               @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

  10:               @data )

  11:     END

  12: GO

因此當我們模擬一下，建立一組測試帳號後變更密碼，再將該帳號給刪除之後，此時我們查看剛剛所建立的 ChecgeLogin_Log 的資料表，就可以看到如下的訊息

如果想要更詳細的紀錄，可以參考 Data 欄位內的 XML 的資料

使用 DDL Trigger 算是比使用 SQL Trace 來的更為方便，如果是 SQL Server 2005 以上的版本，則算是一個不錯的選擇。

除了 DDL Trigger 之外，使用 Server Audit 也是一個不錯的方式，但要注意的是當你如果是 SQL Server 2008/2008R2 的版本，那只有 Enterprise 的版本才有提供；而到了 SQL Server 2012/214 的時候，就放寬到 Standard 以上的版本都可以使用 Server Level Audit，但如果要使用 Database Level Audit，則就還是要使用 Enterprise 版本才能使用。在這個例子中我們所要使用的是 Server Level Audit，因此如果你有 SQL Server 2012/2014 Standard 以上的版本或者是 SQL Server 2008 Enterprise 的版本，則都可以使用該方法。

要設定 Audit，在 SQL Server 2012 是可以使用 GUI 或者是用 T-SQL 來進行設定。首先我們先建立稽核，這裡可以在 SSMS 上選擇「安全性」→「稽核」，按下滑鼠右鍵選擇「新增稽核」，建立好之後選擇該稽核設定，按下滑鼠右鍵選擇「啟用稽核」

或者是使用 T-SQL 指令來設定都可以

   1: CREATE SERVER AUDIT [Audit-20150425-214052]

   2: TO FILE 

   3: (    FILEPATH = N'D:\Temp'

   4:     ,MAXSIZE = 100 MB

   5:     ,MAX_FILES = 1024

   6:     ,RESERVE_DISK_SPACE = OFF

   7: )

   8: WITH

   9: (    QUEUE_DELAY = 1000

  10:     ,ON_FAILURE = CONTINUE

  11: )

  12: GO

  13:  

  14: ALTER SERVER AUDIT [Audit-20150425-214052] 

  15: WITH (STATE = ON)

  16: GO

接下來我們要設定要記錄哪些事件存放到剛才所設定的稽核設定檔案內，一樣在 SSMS 上選擇「安全性」→「伺服器稽核規格」，按下滑鼠右鍵選擇「新增伺服器稽核規格」，建立好之後選擇該設定，按下滑鼠右鍵選擇「啟用伺服器稽核規格」

或者是想專家一點，使用 T-SQL 來做建立

   1: CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20150425-215111]

   2: FOR SERVER AUDIT [Audit-20150425-214052]

   3: ADD (LOGIN_CHANGE_PASSWORD_GROUP),

   4: ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

   5: WITH (STATE = ON)

   6: GO

在這裡跟 DDL Trigger 有點不同，建立和刪除帳號的事件都放在 SERVER_PRINCIPAL_CHANGE_GROUP 內，因此透過上述的方式進行設定好之後，我們依然使用 DDL Trigger 相同的測試方式，測試完之後我們可以在剛才所建立的稽核設定上，按下滑鼠右鍵選擇「檢視稽核計錄」，就可以很方便的看到如下的紀錄

或者是使用指令的方式來查看

   1: select 

   2:   event_time,action_id, succeeded, object_name,statement 

   3: from fn_get_audit_file( 'D:\Temp\Audit*.sqlaudit',default,default);

從上述的步驟和之前的文章中可以看到，使用新版本的 SQL Server 可以讓你在管理上越來越方便，因此同樣的一件事情，你可以選擇很複雜的去設定 SQL Trace，或者是還是要寫程式，用可以寫的比 SQL Trace 少一點的 DDL Trigger，亦或者是要偷懶的全部透過 GUI 來設定 Server Level Audit，都可以做到相同的管理目的，就端看你所使用的 SQL Server 版本，選擇你覺得方式來做管理囉。