配置 SQL Server Audit：登录、权限变更和表访问

命令

USE master;
CREATE SERVER AUDIT [Audit_DBA]
TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit_DBA] WITH (STATE = ON);

-- Server-wide (covers all databases):
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Server]
FOR SERVER AUDIT [Audit_DBA]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

-- Database: access to a sensitive table:
USE [Musteri];
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Musteri]
FOR SERVER AUDIT [Audit_DBA]
    ADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::[dbo].[Siparis] BY [public])
WITH (STATE = ON);

-- Read:
SELECT event_time, action_id, succeeded, server_principal_name, database_name,
       schema_name, object_name, LEFT(statement, 200) AS stmt, client_ip, application_name
FROM   sys.fn_get_audit_file(N'D:\Audit\Audit_DBA_*.sqlaudit', DEFAULT, DEFAULT)
WHERE  event_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY event_time DESC;

作用

创建写入文件的服务器审核；在服务器范围记录登录失败、角色成员身份、主体和权限变更，并在数据库级别记录对所选表的 SELECT/DML 访问，再用 T-SQL 读取记录。

适用场景

用于审计/合规要求（GDPR、PCI、SOX）、“谁读取/修改了这张表”之类的问题，或怀疑权限提升时。

注意

对繁忙表做 SELECT 审核会极快地产生记录并影响性能；按对象和主体缩小范围。ON_FAILURE = SHUTDOWN 会在审核无法写入时停止服务器；仅在强制要求时使用。审核文件夹必须可被 SQL Server 服务账户写入，且非 DBA 无法删除。event_time 为 UTC。数据库审核规范会随数据库一起还原，但若服务器审核 GUID 不匹配就会成为孤立状态。