如何在Synapse中分配角色限制数据访问？权限配置异常求助

核心问题根源 #

Synapse SQL Administrator是服务器级角色，自带CONTROL SERVER权限，该权限会完全覆盖数据库内的所有权限限制——这就是你给用户分配数据库角色后，用户仍能访问所有数据的原因，服务器级权限优先级远高于数据库级权限。

分步解决方法 #

1. 移除高权限服务器角色 #

先在Synapse Studio中移除用户的Synapse SQL Administrator角色，这个角色权限过于宽泛，不适合需要细粒度权限控制的场景。

2. 分配合适的Synapse RBAC角色 #

替换为Synapse SQL User角色（而非Synapse Contributor），这个角色专门用于访问Synapse SQL无服务器数据库和SQL池，不会赋予不必要的管理权限，同时能保证用户正常连接SQL环境。

3. 修正数据库内权限配置 #

你之前的脚本直接给用户分配权限而非角色，且未清除用户可能继承的高权限。修正后的脚本如下：

USE [YourDatabaseName];
GO

-- 检查并创建角色（避免重复创建报错）
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Basic Access')
CREATE ROLE [Basic Access];
GO

-- 将用户添加到自定义角色
ALTER ROLE [Basic Access] ADD MEMBER [john doe];
GO

-- 给角色分配基础连接权限
GRANT CONNECT TO [Basic Access];
GRANT VIEW DATABASE STATE TO [Basic Access];
GO

-- 给角色分配特定对象的查询权限
GRANT SELECT ON OBJECT::dbo.tbl_a TO [Basic Access];
GO

-- 撤销用户直接拥有的数据库高权限（清除冗余权限）
REVOKE ALL ON DATABASE::[YourDatabaseName] FROM [john doe];
GO

4. 内置SQL池的权限配置 #

对于内置SQL池（如default池），需要在SQL池内单独配置用户权限：

1. 连接到目标SQL池，执行脚本创建AD身份用户：

   USE [YourSQLPoolName];
   GO
   CREATE USER [john doe] FROM EXTERNAL PROVIDER;
   GO
   

2. 给用户分配必要权限，比如只读权限：

   ALTER ROLE db_datareader ADD MEMBER [john doe];
   -- 或仅授权特定表/视图
   GRANT SELECT ON OBJECT::dbo.YourPoolTable TO [john doe];
   GO
   

也可以通过Synapse RBAC给用户分配Synapse SQL Contributor角色，该角色允许用户访问SQL池并执行数据操作。

关键说明 #

• EXECUTE AS生效的原因是：该语句仅模拟用户的数据库级权限，不会继承服务器级高权限，所以权限限制生效；而用户实际登录时，服务器级权限会覆盖数据库配置。
• 权限配置完成后，建议用户退出并重新登录Synapse，确保权限生效。

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