SQL Server Express远程多用户环境下近3天数据库与文件变更追踪脚本咨询

嘿，我来帮你解决这个SQL Server Express里追踪多用户变更的问题！之前你用sys.dm_exec_query_stats没拿到预期结果，其实是这个方法本身的局限性导致的——它依赖SQL Server的查询缓存，一旦缓存被清理（比如服务器重启、内存不够、手动清缓存），之前的查询记录就没了，而且它没法直接关联到具体的执行者，完全不适合长期追踪变更的场景。

下面给你几个靠谱的方案，按推荐程度排序，你可以根据自己的需求选：

一、用SQL Server Audit追踪（推荐，Express也能用基础功能） #

虽然SQL Server Express不支持完整的Audit高级功能，但基础的DML（增删改）和DDL（建表改结构）操作追踪还是能实现的：

1. 先创建一个审核对象，指定日志保存的路径（记得提前建好文件夹，给SQL Server服务权限）：

   CREATE SERVER AUDIT [ServerAudit]
   TO FILE (FILEPATH = N'C:\SQLAuditLogs\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 5)
   WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
   

2. 启用这个审核：

   ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
   

3. 针对你的数据库创建审核规范，指定要追踪的操作：

   USE YourDatabaseName; -- 替换成你的数据库名
   CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
   FOR SERVER AUDIT [ServerAudit]
   ADD (INSERT ON DATABASE::YourDatabaseName BY [public]),
   ADD (UPDATE ON DATABASE::YourDatabaseName BY [public]),
   ADD (DELETE ON DATABASE::YourDatabaseName BY [public]),
   ADD (ALTER_TABLE ON DATABASE::YourDatabaseName BY [public]) -- 追踪表结构变更
   WITH (STATE = ON);
   

4. 查询近3天的变更记录，就能拿到执行者、时间、具体操作内容了：

   SELECT
       event_time AS 操作时间,
       session_server_principal_name AS 执行者账号,
       statement AS 具体变更SQL,
       CASE action_id
           WHEN 'INS' THEN '插入'
           WHEN 'UPD' THEN '更新'
           WHEN 'DEL' THEN '删除'
           WHEN 'ALT' THEN '修改对象结构'
           ELSE action_id
       END AS 操作类型
   FROM sys.fn_get_audit_file('C:\SQLAuditLogs\ServerAudit_*.sqlaudit', DEFAULT, DEFAULT)
   WHERE event_time >= DATEADD(DAY, -3, GETDATE())
   ORDER BY event_time DESC;
   

二、用变更数据捕获（CDC）追踪表数据的详细变更 #

如果你重点要追踪表数据的前后变化（比如更新前的值和更新后的值），CDC是个好选择，它专门干这个：

1. 先给你的数据库启用CDC：

   USE YourDatabaseName;
   EXEC sp_cdc_enable_db;
   

2. 针对需要追踪的表启用CDC（比如dbo.UserInfo表）：

   EXEC sp_cdc_enable_table
       @source_schema = N'dbo',
       @source_name = N'UserInfo', -- 替换成你的表名
       @role_name = NULL,
       @supports_net_changes = 1;
   

3. 查询近3天的变更数据，能看到每一条数据的变更前后状态：

   SELECT
       __$start_lsn AS 变更标识,
       CASE __$operation
           WHEN 1 THEN '删除'
           WHEN 2 THEN '插入'
           WHEN 3 THEN '更新前状态'
           WHEN 4 THEN '更新后状态'
       END AS 操作类型,
       -- 这里可以列出你需要的具体列，比如 UserID, UserName...
       *
   FROM cdc.fn_cdc_get_all_changes_dbo_UserInfo(
       sys.fn_cdc_get_min_lsn('dbo_UserInfo'),
       sys.fn_cdc_get_max_lsn(),
       N'all update old'
   )
   WHERE __$start_lsn >= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', DATEADD(DAY, -3, GETDATE()))
   ORDER BY __$start_lsn DESC;
   

   注意：CDC本身不直接记录执行者，如果你需要关联执行者，可以结合前面的Audit日志，或者给表加个触发器记录操作人。

三、自定义触发器（适合简单场景，快速上手） #

如果上面两个方法你暂时没法用，那可以用触发器来手动记录变更，虽然会增加一点数据库负载，但胜在简单：

1. 先建一个日志表，用来存所有变更记录：

   CREATE TABLE ChangeLog (
       LogID INT IDENTITY(1,1) PRIMARY KEY,
       TableName NVARCHAR(128),
       OperationType NVARCHAR(10),
       ChangedBy NVARCHAR(128), -- 执行者账号
       ChangedTime DATETIME DEFAULT GETDATE(),
       OldData XML, -- 变更前的数据（XML格式）
       NewData XML  -- 变更后的数据（XML格式）
   );
   

2. 给需要追踪的表创建触发器（还是以dbo.UserInfo为例）：

   CREATE TRIGGER trg_UserInfo_ChangeLog
   ON dbo.UserInfo
   AFTER INSERT, UPDATE, DELETE
   AS
   BEGIN
       SET NOCOUNT ON;
   
       -- 处理删除操作
       IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
       BEGIN
           INSERT INTO ChangeLog (TableName, OperationType, ChangedBy, OldData)
           SELECT 
               'UserInfo',
               'DELETE',
               SUSER_SNAME(), -- 获取当前执行者账号
               (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS)
       END
   
       -- 处理插入操作
       IF EXISTS(SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
       BEGIN
           INSERT INTO ChangeLog (TableName, OperationType, ChangedBy, NewData)
           SELECT 
               'UserInfo',
               'INSERT',
               SUSER_SNAME(),
               (SELECT * FROM INSERTED FOR XML AUTO, ELEMENTS)
       END
   
       -- 处理更新操作
       IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
       BEGIN
           INSERT INTO ChangeLog (TableName, OperationType, ChangedBy, OldData, NewData)
           SELECT 
               'UserInfo',
               'UPDATE',
               SUSER_SNAME(),
               (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS),
               (SELECT * FROM INSERTED FOR XML AUTO, ELEMENTS)
       END
   END
   

3. 查询近3天的变更记录：

   SELECT
       LogID,
       TableName,
       OperationType,
       ChangedBy AS 执行者,
       ChangedTime AS 操作时间,
       OldData AS 变更前数据,
       NewData AS 变更后数据
   FROM ChangeLog
   WHERE ChangedTime >= DATEADD(DAY, -3, GETDATE())
   ORDER BY ChangedTime DESC;
   

四、查询近3天变更的数据库对象（表、视图、存储过程等） #

如果你说的“变更文件”是指数据库里的对象（比如表结构改了、存储过程更新了），可以用这个脚本查：

SELECT
    name AS 对象名称,
    type_desc AS 对象类型,
    create_date AS 创建时间,
    modify_date AS 最后修改时间,
    SUSER_SNAME(principal_id) AS 对象所有者
FROM sys.objects
WHERE modify_date >= DATEADD(DAY, -3, GETDATE())
AND type IN ('U', 'V', 'P', 'FN') -- U=用户表, V=视图, P=存储过程, FN=标量函数
ORDER BY modify_date DESC;

内容的提问来源于stack exchange，提问作者PatsonLeaner