如何为指定人员分配Schema/Table权限？SQL Server追踪表权限管控咨询

一、完全禁止所有用户访问的实现方法 #

要确保无普通用户能访问追踪架构和表，核心是回收公共权限、清理已分配的权限，并把控架构所有者：

• 回收public角色对目标架构的基础权限
  SQL Server默认public角色会拥有部分公共权限，先彻底回收：

  REVOKE ALL ON SCHEMA::[你的追踪架构名] TO public;
  

• 清理所有用户/角色对追踪表的直接权限
  如果之前给过其他用户权限，逐一回收：

  -- 回收单个用户对追踪表的权限
  REVOKE ALL ON [你的追踪架构名].[追踪表名] TO [目标用户名];
  -- 多表场景可批量执行或循环处理
  

• 锁定架构所有者权限
  将架构所有者设置为高权限管理账号（如sa或专属管理账号），避免普通用户通过继承所有者权限访问：

  ALTER AUTHORIZATION ON SCHEMA::[你的追踪架构名] TO [管理账号名];
  

二、通过数据库角色实现仅指定用户可访问的方案 #

完全可行，这是SQL Server权限管控的标准实践，步骤如下：

1. 创建专属数据库角色

   CREATE ROLE [DDL_Trace_Viewer];
   

2. 给角色授予访问权限
   可选择授予整个架构的查询权限（覆盖所有表），或单独给指定表授权：

   -- 授予角色对架构的SELECT权限（适用于架构下全部表）
   GRANT SELECT ON SCHEMA::[你的追踪架构名] TO [DDL_Trace_Viewer];
   -- 仅针对单个表授权
   GRANT SELECT ON [你的追踪架构名].[追踪表名] TO [DDL_Trace_Viewer];
   

3. 将需要访问的用户加入角色

   ALTER ROLE [DDL_Trace_Viewer] ADD MEMBER [需访问的用户名];
   

4. 清理无关权限
   参考第一部分的操作，确保除该角色外，其他用户（包括public）无相关访问权限。

关键注意事项 #

• sysadmin固定服务器角色的用户拥有数据库最高权限，无法限制这类账号的访问，需严格管控此类账号的使用范围。
• 定期审计权限分配，可通过以下查询检查目标架构的权限情况：

  SELECT 
      dp.name AS 主体名称,
      dp.type_desc AS 主体类型,
      perm.permission_name,
      perm.state_desc,
      s.name AS 架构名
  FROM 
      sys.database_permissions perm
  JOIN 
      sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
  JOIN 
      sys.schemas s ON perm.major_id = s.schema_id
  WHERE 
      s.name = '[你的追踪架构名]';
  

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