SQL Server DDL触发器失效问题：如何限制指定角色用户删除数据库对象

看起来你的DDL触发器没生效主要是几个关键细节没处理好，我帮你逐一排查并给出修正方案：

问题根源分析 #

1. 大小写匹配错误
   你在查询里用了LOWER(r.name)把角色名转成小写，但判断条件里写的是IN ('Role1', 'Role2')（大写开头），这样小写的role1永远匹配不上大写的Role1，直接导致IF条件不成立，触发器根本不会执行限制逻辑。

2. 错误的表关联逻辑
   sys.database_principals的principal_id是数据库范围内的唯一标识，和sys.server_principals的principal_id完全不相关，你把这两个表用m.member_principal_id = l.principal_id关联，会过滤掉所有正确的角色成员记录，导致查询返回空值，IF条件自然不会触发。

3. 服务器级触发器的上下文问题
   服务器级触发器默认运行在master数据库的上下文下，而你查询的sys.database_role_members是当前数据库的视图——如果用户在其他业务数据库执行DROP操作，你查的是master库的角色信息，根本不是用户所在数据库的角色，这也会导致查询结果完全错误。

修正后的服务器级触发器代码 #

下面的代码解决了上述所有问题，能准确拦截指定角色用户的DROP操作：

CREATE TRIGGER Deny_Drop_Permissions
ON ALL SERVER
FOR DROP_TABLE, DROP_INDEX, DROP_VIEW, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取当前操作的数据库名和执行操作的用户名
    DECLARE @DBName NVARCHAR(128) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)');
    DECLARE @SessionUser NVARCHAR(128) = EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(128)');
    DECLARE @IsRestricted BIT = 0;
    DECLARE @SQL NVARCHAR(MAX);

    -- 动态切换到用户操作的数据库，查询该库内的角色成员关系
    SET @SQL = N'
    USE [' + @DBName + N'];
    SELECT @IsRestricted = 1
    FROM sys.database_role_members m
    INNER JOIN sys.database_principals r ON m.role_principal_id = r.principal_id
    INNER JOIN sys.database_principals u ON m.member_principal_id = u.principal_id
    WHERE u.name = @SessionUser
    AND r.name IN (''Role1'', ''Role2'')';

    EXEC sp_executesql @SQL, N'@SessionUser NVARCHAR(128), @IsRestricted BIT OUTPUT', @SessionUser, @IsRestricted OUTPUT;

    -- 如果是被限制的角色用户，阻止操作并提示
    IF @IsRestricted = 1
    BEGIN
        RAISERROR('You don''t have the privileges to drop objects. Please contact your DBA.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

关键修正点说明 #

• 用EVENTDATA()准确获取用户执行DROP操作的目标数据库和用户名，确保查询的是正确数据库的角色信息。
• 去掉了错误的sys.server_principals关联，直接关联数据库级的用户和角色视图，保证查询结果正确。
• 使用动态SQL切换到用户操作的数据库进行查询，彻底解决服务器级触发器的上下文问题。
• 去掉了不必要的LOWER()转换，直接用角色原名匹配（如果你的SQL Server实例是大小写敏感的，可以改成LOWER(r.name) IN (''role1'', ''role2'')来统一匹配）。

简化方案：数据库级触发器（如果只需要限制单个数据库） #

如果你不需要在所有服务器数据库生效，只需要限制特定数据库，用数据库级触发器会更简单，代码也更简洁：

-- 针对单个数据库创建的触发器
CREATE TRIGGER Deny_Drop_Permissions
ON DATABASE
FOR DROP_TABLE, DROP_INDEX, DROP_VIEW, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;

    -- 直接用IS_MEMBER函数判断当前用户是否属于指定角色
    IF IS_MEMBER('Role1') = 1 OR IS_MEMBER('Role2') = 1
    BEGIN
        RAISERROR('You don''t have the privileges to drop objects. Please contact your DBA.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
GO

这个方案利用IS_MEMBER()函数直接判断当前用户在数据库中的角色归属，没有上下文问题，代码更易维护。

备注：内容来源于stack exchange，提问作者sepideh b