创建SSRS报表识别数据库活动，查询对象创建/修改的实际用户ID

我需要编写SQL查询，识别特定月份内新建的表（U）、视图（V）、存储过程（P）。目前已经实现了获取对象基本信息的查询：

select SCHEMA_NAME(schema_id) AS schema_name, 
       name AS objects_name, 
       create_date, 
       modify_date,
       type,
       so.type_desc,
       principal_id
from sys.objects so
where type in ('U','V','P')
and create_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)

但遇到的难点是：无法找出创建或修改这些对象的实际用户ID（而非角色）。网上资料大多说要创建数据库触发器，请问有没有可以直接运行的查询来确定责任人？

我已经尝试过多种查询，包括以下跟踪脚本：

DECLARE @RC int, @TraceID int, @on BIT
EXEC @rc = sp_trace_create @TraceID output, 2, N'C:\path\file'
SELECT RC = @RC, TraceID = @TraceID
-- Follow Common SQL trace event list and common sql trace
-- tables to define which events and table you want to capture
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 128, 1, @on
-- (128-Event Audit Database Management Event, 1-TextData table column)
EXEC sp_trace_setevent @TraceID, 128, 11, @on
EXEC sp_trace_setevent @TraceID, 128, 14, @on
EXEC sp_trace_setevent @TraceID, 128, 35, @on 
EXEC @RC = sp_trace_setstatus @TraceID, 1
GO

核心说明 #

SQL Server的sys.objects表中，principal_id字段记录的是对象的所有者，而非实际创建/修改对象的操作人。默认情况下，SQL Server不会持久化存储对象操作的责任人信息，但可以通过以下两种途径获取相关记录：

1. 查询默认跟踪（Default Trace）获取近期操作记录 #

SQL Server默认会启用一个轻量的默认跟踪，用于记录数据库级的关键操作，其中包含对象的创建/修改/删除事件。无需提前配置，直接查询即可：

步骤1：获取默认跟踪文件路径 #

SELECT path 
FROM sys.traces 
WHERE is_default = 1;

步骤2：查询跟踪文件中的对象操作记录 #

将上一步得到的路径替换到fn_trace_gettable参数中，筛选目标时间范围和对象类型：

SELECT
    te.name AS event_name,
    t.DatabaseName,
    t.ObjectName,
    t.ObjectType,
    t.LoginName,
    t.StartTime,
    t.TextData
FROM fn_trace_gettable(N'替换为步骤1获取的跟踪文件路径', DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE
    -- 筛选对象创建/修改/删除事件（128对应Audit Database Management Event）
    t.EventClass = 128
    -- 限定时间范围，替换为你需要的日期区间
    AND t.StartTime >= DATEADD(MONTH, -1, GETDATE())
    -- 筛选目标对象类型：8259=用户表（U），8272=视图（V），8298=存储过程（P）
    AND t.ObjectType IN (8259, 8272, 8298)
ORDER BY t.StartTime DESC;

注：默认跟踪会循环覆盖旧文件，如果所需操作记录已被覆盖，这种方法无法获取数据。

2. 事后补救：创建数据库触发器记录操作人 #

如果默认跟踪没有保留所需数据，只能通过创建触发器从当前时刻开始记录所有对象操作的责任人信息：

步骤1：创建操作日志表 #

CREATE TABLE ObjectChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    SchemaName NVARCHAR(128),
    ObjectName NVARCHAR(128),
    ObjectType NVARCHAR(100),
    OperatorLogin NVARCHAR(128),
    OperatorSID VARBINARY(85),
    ChangeTime DATETIME DEFAULT GETDATE()
);

步骤2：创建数据库级触发器 #

CREATE TRIGGER TrackObjectChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
    CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventType NVARCHAR(100),
            @SchemaName NVARCHAR(128),
            @ObjectName NVARCHAR(128),
            @ObjectType NVARCHAR(100);

    -- 提取事件详情
    SELECT 
        @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'),
        @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'),
        @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)');

    -- 插入日志记录
    INSERT INTO ObjectChangeLog (EventType, SchemaName, ObjectName, ObjectType, OperatorLogin, OperatorSID)
    VALUES (@EventType, @SchemaName, @ObjectName, @ObjectType, ORIGINAL_LOGIN(), SUSER_SID(ORIGINAL_LOGIN()));
END;
GO

之后所有表、视图、存储过程的创建/修改/删除操作，都会被记录到ObjectChangeLog表中，直接查询该表即可获取操作人信息。

关于你使用的跟踪脚本 #

你手动创建的SQL跟踪功能更强大，但需要管理员权限，且会生成本地跟踪文件，维护成本更高。优先使用默认跟踪，只有当默认跟踪无法满足需求时，再考虑手动创建跟踪。

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