如何清除SQL中指定用户的所有GRANT与DENY权限？

核心思路 #

sys.database_permissions是系统视图，无法直接删除其中的记录，只能通过生成并执行REVOKE语句，撤销该用户所有已配置的GRANT和DENY权限，之后再重新配置所需权限。

具体操作步骤 #

1. 自动生成撤销权限的脚本
   运行以下SQL查询，将@UserName替换为目标用户名，查询会生成针对该用户所有权限的REVOKE语句：

   DECLARE @UserName NVARCHAR(128) = N'你的目标用户名';
   
   SELECT 
       CASE 
           WHEN perm.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'REVOKE GRANT OPTION FOR '
           ELSE 'REVOKE '
       END
       + perm.permission_name + ' ON '
       + CASE 
           WHEN class_desc = 'OBJECT_OR_COLUMN' THEN 'OBJECT::' + SCHEMA_NAME(obj.schema_id) + '.' + obj.name
           WHEN class_desc = 'DATABASE' THEN 'DATABASE::' + DB_NAME()
           WHEN class_desc = 'SCHEMA' THEN 'SCHEMA::' + SCHEMA_NAME(perm.major_id)
           ELSE class_desc + '::' + OBJECT_NAME(perm.major_id)
       END
       + ' TO [' + @UserName + ']'
       + CASE WHEN perm.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN ' CASCADE;' ELSE ';' END AS RevokeStatement
   FROM sys.database_permissions perm
   JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
   LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
   WHERE dp.name = @UserName;
   

2. 执行生成的REVOKE语句
   将查询结果中的所有语句复制出来，在目标数据库中执行，即可清除该用户所有已有的GRANT和DENY权限。
3. 重新配置权限
   此时可以运行你的权限配置脚本，为用户设置合适的GRANT和DENY权限。

注意事项 #

• 执行REVOKE前，建议保存当前权限的查询结果，作为备份，避免误操作后无法恢复。
• 该方法仅撤销直接授予用户的权限，用户通过数据库角色继承的权限不会被影响，需单独处理角色相关配置。
• 操作时需确保自身拥有足够权限（如ALTER ANY USER、CONTROL SERVER等）。

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