[研究]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），並能長期儲存記錄。
(完)
