浮雲雅築: [研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

2025年3月28日星期五

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

2025-03-27

SQL Server 2019 如何查詢資料庫帳號變更稽核？

ChatGPT 說：

在 SQL Server 2019 中，可以使用 SQL Server Audit 或 Default Trace 來查詢資料庫帳號變更的稽核記錄，以下是幾種方法：

********************************************************************************

2025-04-02 補

註：ChatGPT 說在 SQL Server 中，「伺服器稽核（Server Audit）」功能只有在 Enterprise 版和特定的高階版本（如 Datacenter 或 Developer）才可用，而 Standard 版及更低版本（如 Express）不支援這個功能。敝人實際測試並非如此。根據下面網址，至少 SQL Server 2016 有，但沒提到要 Enterprise 才支援

https://learn.microsoft.com/zh-tw/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2016

3.2. SQL Server Audit Support in Different Editions and Versions
https://logbinder.helpspot.com/index.php?pg=kb.page&id=79
根據這篇，有更詳細說明

Edition \ Version	SQL Server 2008 and 2008 R2	SQL Server 2012 and 2014	SQL Server 2016* and 2017
Enterprise	Server- and database-level	Server- and database-level	Server- and database-level
Developer	Server- and database-level	Server- and database-level	Server- and database-level
Datacenter	Server- and database-level	N/A	N/A
Business Intelligence	None	Server-level	N/A
Standard	None	Server-level	Server- and database-level*
Web	None	Server-level	Server- and database-level*
Express	None	Server-level	Server- and database-level*

* Database-level auditing for Standard, Web and Express editions are available starting SQL Server 2016 SP1.

********************************************************************************

方法 1：使用 SQL Server Audit（推薦方式）

SQL Server Audit 提供較完整的稽核功能，可以記錄登入帳號新增、刪除、變更密碼、授權變更等操作。

1. 建立 Server Audit（伺服器級稽核）

首先，建立稽核物件，將稽核記錄存放於檔案中：

請先手動建立C:\AuditLogs\ 目錄

下圖。ChatGPT 原本給的指令如下，就算是 SQL Server 2019 Enterprise 也無法執行成功

CREATE SERVER AUDIT LoginAudit
TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 10MB, ROLLOVER = ON);
ALTER SERVER AUDIT LoginAudit WITH (STATE = ON);

敝人後來改成下面

CREATE SERVER AUDIT Audit_LoginChanges
TO FILE ( FILEPATH = 'C:\AuditLogs\', MAXSIZE = 10MB)　　
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE );

(下圖)ChatGPT 說 SQL Server Audit 要 Enterprise 版才支援，但敝人實際測試下面指令 SQL Server 2019 Standard 也可以正常執行

2. 建立 Server Audit Specification（伺服器層級稽核規格）

設定要稽核的事件：

CREATE SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec
FOR SERVER AUDIT Audit_LoginChanges
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

其中 SERVER_PRINCIPAL_CHANGE_GROUP 會記錄登入帳號變更（如 CREATE LOGIN、ALTER LOGIN、DROP LOGIN 等）。

3. 啟動 Audit

ALTER SERVER AUDIT Audit_LoginChanges WITH (STATE = ON);

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

4. 查詢 Audit 記錄

當帳號變更後，可以用以下 SQL 查詢：

SELECT event_time, action_id, succeeded, session_server_principal_name, server_principal_name, statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

其中

event_time：事件時間
action_id：動作類型（如 LGCR 表示 CREATE LOGIN，LGMD 表示 ALTER LOGIN）
succeeded：是否成功（1 = 成功，0 = 失敗）
session_server_principal_name：執行變更的帳號
server_principal_name：被變更的帳號
statement：執行的 SQL 指令

5. 刪除建立的東西

--步驟 1：停用 SERVER AUDIT SPECIFICATION
ALTER SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec WITH (STATE = OFF);

--步驟 2：刪除 SERVER AUDIT SPECIFICATION
DROP SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec;

--步驟 3：停用 SERVER AUDIT
ALTER SERVER AUDIT Audit_LoginChanges WITH (STATE = OFF);

--步驟 4：刪除 SERVER AUDIT
DROP SERVER AUDIT Audit_LoginChanges;

********************************************************************************

方法 2：使用 Default Trace（預設追蹤）

SQL Server 預設啟用了 Default Trace，可以用來查詢帳號變更。

查詢 Default Trace

SELECT 
    te.name AS EventName,
    t.DatabaseName,
    t.StartTime,
    t.ObjectName,
    t.ObjectType,
    t.EventClass,
    t.EventSubClass,
    t.TextData
FROM sys.fn_trace_gettable(
    (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), LEN(path))) + 'log.trc' 
     FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE t.EventClass IN (104, 105, 108) -- 104: Create Login, 105: Drop Login, 108: Alter Login
ORDER BY t.StartTime DESC;

其中

104（Create Login）：建立登入帳號
105（Drop Login）：刪除登入帳號
108（Alter Login）：修改登入帳號
Default Trace 只保留短期記錄，如果需要長期記錄，建議使用 SQL Server Audit。

(下圖) 新增一個帳號後測試

********************************************************************************

方法 3：查詢 SQL Server Error Log（部分變更可查）

部分帳號變更（如 CREATE LOGIN 或 ALTER LOGIN）會記錄到 SQL Server Error Log。

請參考

[研究]SQL Server 2019 資料庫帳號登出登入稽核？
https://shaurong.blogspot.com/2025/03/sql-server-2019.html

這方法適用於 SQL Server 已啟用 LOGIN_CHANGE_PASSWORD 記錄的情況，但不如 SQL Server Audit 詳細。

********************************************************************************

總結

方法	優點	缺點
SQL Server Audit（推薦）	最完整，支援長期記錄，可用 GUI 或 SQL 查詢	需設定 Audit
Default Trace	內建功能，不需額外設定	只能查詢最近變更
SQL Server Error Log	不需設定，適用於密碼變更查詢	記錄不完整

如需完整監控帳號變更，建議使用 SQL Server Audit，可確保符合稽核規範（如 GDPR、ISO 27001），並能長期儲存記錄。

(完)

浮雲雅築

2025年3月28日星期五

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

沒有留言:

張貼留言

2025年3月28日 星期五

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

沒有留言:

張貼留言

2025年3月28日星期五