SQL Server中db_datareader账号可执行更新操作问题排查求助

• 检查public角色的数据库级别权限
  问题服务器上的DB_A和TEST库，public角色可能被误授予了数据修改权限（比如db_datawriter）。执行以下SQL确认：

  USE DB_A;
  SELECT dp.name AS DatabaseRole, perm.permission_name, perm.state_desc
  FROM sys.database_role_members drm
  JOIN sys.database_principals dp ON drm.role_principal_id = dp.principal_id
  JOIN sys.database_permissions perm ON dp.principal_id = perm.grantee_principal_id
  WHERE dp.name = 'public';
  
  USE TEST;
  -- 重复上述查询
  

  由于public是所有数据库用户的默认角色，一旦被赋予写权限，所有用户都会继承该权限。

• 验证用户的有效权限
  用select_A登录后，执行以下命令查看实际拥有的权限，确认是否存在隐性的写权限来源：

  USE DB_A;
  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
  SELECT * FROM fn_my_permissions('dbo.目标表名', 'OBJECT'); -- 替换为实际表名
  

  也可以用管理员账号查询该用户的权限链：

  USE DB_A;
  SELECT dp.name AS UserName, perm.permission_name, perm.state_desc, 
         dp2.name AS GrantedBy, dp3.name AS RoleName
  FROM sys.database_permissions perm
  JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
  LEFT JOIN sys.database_principals dp2 ON perm.grantor_principal_id = dp2.principal_id
  LEFT JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
  LEFT JOIN sys.database_principals dp3 ON drm.role_principal_id = dp3.principal_id
  WHERE dp.name = 'select_A';
  

• 检查服务器级别的权限继承
  查看select_A和select_B的登录账号是否被授予了服务器级别的高权限（比如ALTER ANY DATABASE或CONTROL SERVER），这类权限会覆盖数据库级别的限制：

  SELECT sp.name AS LoginName, perm.permission_name, perm.state_desc
  FROM sys.server_permissions perm
  JOIN sys.server_principals sp ON perm.grantee_principal_id = sp.principal_id
  WHERE sp.name IN ('select_A', 'select_B');
  

  同时确认是否属于未察觉的服务器角色（比如sysadmin）：

  SELECT sp.name AS LoginName, sr.name AS ServerRole
  FROM sys.server_role_members srm
  JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
  JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
  WHERE sp.name IN ('select_A', 'select_B');
  

• 确认数据库用户映射与默认架构权限
  检查select_A在DB_A中的用户是否映射到了错误的架构（比如dbo），且该架构的权限被过度赋予：

  USE DB_A;
  SELECT dp.name AS UserName, dp.default_schema_name, dp.type_desc,
         sp.name AS LoginName
  FROM sys.database_principals dp
  JOIN sys.server_principals sp ON dp.sid = sp.sid
  WHERE dp.name = 'select_A';
  

  另外查看dbo的权限配置：

  USE DB_A;
  SELECT perm.permission_name, perm.state_desc
  FROM sys.database_permissions perm
  JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
  WHERE dp.name = 'dbo';
  

• 检查数据库的TRUSTWORTHY属性
  如果数据库的TRUSTWORTHY属性被设置为ON，可能导致跨数据库权限泄漏，覆盖原本的权限限制：

  SELECT name, is_trustworthy_on FROM sys.databases WHERE name IN ('DB_A', 'TEST');
  

• 排查服务器级触发器与自定义角色
  检查服务器上是否存在自动赋予权限的DDL触发器，或者自定义的服务器角色：

  SELECT name, type_desc FROM sys.server_triggers;
  SELECT name FROM sys.server_principals WHERE type = 'R' AND name NOT IN ('sysadmin', 'securityadmin', 'serveradmin', 'setupadmin', 'processadmin', 'diskadmin', 'dbcreator', 'bulkadmin');
  

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