是否可对触发器单独授权，使其在操作用户无对应权限时正常运行

核心问题原因 #

你的触发器执行失败的根源是代码中查询sys.dm_exec_connections动态管理视图需要调用者拥有VIEW SERVER STATE服务器级权限，普通仅拥有schema变更权限的用户默认不具备该权限，因此触发执行报错。

可行解决方案 #

无需给所有schema变更用户开放高权限，通过指定触发器的执行上下文即可解决，具体操作步骤如下：

步骤1：配置最小权限专用账号（推荐，安全性最高） #

仅需给这个专用账号分配必要的权限，不需要调整普通用户的权限配置：

-- 实例级别创建专用登录名
CREATE LOGIN SchemaLogTriggerRunner WITH PASSWORD = '替换为你的强密码';
-- 仅给该账号授予触发器需要的VIEW SERVER STATE权限
GRANT VIEW SERVER STATE TO SchemaLogTriggerRunner;

-- 切换到你的业务数据库
USE [你的业务数据库名称];
-- 创建数据库对应的用户
CREATE USER SchemaLogTriggerRunner FOR LOGIN SchemaLogTriggerRunner;
-- 授予日志表插入权限
GRANT INSERT ON SchemaEvents TO SchemaLogTriggerRunner;

步骤2：修改触发器指定执行上下文 #

修改原有DDL触发器，添加WITH EXECUTE AS子句，指定触发器运行时使用上述专用账号的权限上下文执行，普通用户触发触发器时会自动继承该账号的权限，无需自身拥有高权限：

ALTER TRIGGER [LogSchemaEvent]
    ON DATABASE
    WITH EXECUTE AS 'SchemaLogTriggerRunner' -- 新增此行指定执行上下文
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
        CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
        CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, 
        CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
        CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
        CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
        CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
        CREATE_TYPE, DROP_TYPE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @EventData XML = EVENTDATA()

    DECLARE @ip VARCHAR(32) = (
        SELECT client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID)
 
    INSERT SchemaEvents (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName)
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

注意事项 #

• 触发器中的SUSER_SNAME()、EVENTDATA()等函数会保留实际执行schema变更操作的用户信息，不会被模拟的执行上下文覆盖，日志记录的内容完全准确。
• 如果你不想单独创建专用账号，也可以将WITH EXECUTE AS 'SchemaLogTriggerRunner'替换为WITH EXECUTE AS 'dbo'，只要dbo对应的登录账号拥有VIEW SERVER STATE权限即可正常运行，不过单独建专用账号更符合最小权限安全原则。

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