Earlier in our Azure Datawarehouse, whenever we wanted to grant access to multiple users on a specific schemas having tables.

We used to run the below commands.

create role [DB TEAM_Demo]

create user [TEAM_Demo_AD] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember 'DB TEAM_Demo', 'TEAM_Demo_AD'

GRANT ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE ON SCHEMA:: SANDBOX_Demo TO [DB TEAM_demo]

However, in Fabric data warehouse, the CREATE USER command is not supported. Therefore, I am unable to grant access to an Azure AD group and have to grant access to individual users instead, which is not very helpful for us.

Can I grant users in an AD group access to a specific schema?

Earlier in our Azure Datawarehouse, whenever we wanted to grant access to multiple users on a specific schemas having tables.

We used to run the below commands.

create role [DB TEAM_Demo]

create user [TEAM_Demo_AD] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember 'DB TEAM_Demo', 'TEAM_Demo_AD'

GRANT ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE ON SCHEMA:: SANDBOX_Demo TO [DB TEAM_demo]

However, in Fabric data warehouse, the CREATE USER command is not supported. Therefore, I am unable to grant access to an Azure AD group and have to grant access to individual users instead, which is not very helpful for us.

Can I grant users in an AD group access to a specific schema?

• azure
• data-warehouse
• microsoft-fabric

Share Improve this question Follow asked yesterday SRPSRP 1,18355 gold badges2828 silver badges4646 bronze badges 1

• grant access to an Azure Active Directory (Azure AD) group for a specific schema – Dileep Raj Narayan Thumula Commented yesterday

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

I got the solution using Copilot on how to grant access to a specific schema in Fabric Data warehouse to an AD group. Details below -

Steps to Grant Access to a Specific Schema

1. Create a Role for the AD Group:

   • First, create a role that will be used to manage permissions for the AD group.

   CREATE ROLE [DB_FABRIC_POP_AD];
   
   

2. Add the AD Group to the Role:

   • Add the Azure AD group to the newly created role.

   ALTER ROLE [DB_FABRIC_POP_AD] ADD MEMBER [Your_AD_Group_Name];
   
   

3. Grant Permissions on the Specific Schema:

   • Grant the necessary permissions on the specific schema to the role.

   GRANT SELECT ON SCHEMA::[ABC] TO [DB_FABRIC_POP_AD];
   

Post running the above queries with updated schema and AD group name, I was able to get the desired result.

You can try the below

Create a user from an Active Directory group and assign a role using the following command:

ALTER ROLE db_datareader ADD MEMBER [<ADGroupName>];

After you add to the group (ex: FabricUsers ) and grant access to a lakehouse (ex:LakeHouse_1) you will also need additional permissions.

Like below

After this if you try to login login as a fabricuser_1 You will be able to access lakehouse but cant see or browse any item.

Grant Viewer access (the lowest level of permissions) to the My_Workspace workspace, which contains Lakehouse_1. So granting Viewer access to the workspace now enables members of the FabricUsers group to connect via SQL endpoints and access all data in the lakehouse.

As you mentioned you want to perform the below

GRANT ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE ON SCHEMA:: SANDBOX_Demo TO [DB TEAM_demo]

You will need to provide the RBAC Role named Workspace Contributor

Also know more about the GRANT (Transact-SQL)

This assigns permissions on a securable object to a specified principal. The general syntax follows:

GRANT <permission> ON <object> TO <user, login, or group>. 

Also for detailed overview of permissions, refer to Permissions (Database Engine).

Reference: Demystifying roles and access in Microsoft Fabric lake house