Forum Discussion
Helper IIHow to Add a Microsoft Entra Security Group to a Database Role in Microsoft Fabric Warehouse?
I have created a database role named test in my Microsoft Fabric Warehouse and currently I am adding individual users as members of the role:
create role test;
alter role test
add member [d********@******]
add member [R********@******];
revoke select
on dbo.[********]
to test;
grant select
on dbo.[********]
to test;
revoke select
on schema::dbo
to test;
revoke execute
on object::dbo.[***********************]
to test;Instead of adding individual users, I would like to add a Microsoft Entra ID security group as a member of the test database role, so that all users in the security group automatically inherit the permissions assigned to the role.
The security group does not have an email address.
My question is: What is the correct way or syntax to add a Microsoft Entra ID security group as a member of a database role in a Microsoft Fabric Warehouse?
Hi Ramkishor
In a Microsoft Fabric Warehouse, you typically:
1. **Create a database user for the Microsoft Entra ID security group**
2. **Add that user to the database role**### Syntax
```sql
CREATE USER [My Entra Security Group] FROM EXTERNAL PROVIDER;
ALTER ROLE [MyDatabaseRole] ADD MEMBER [My Entra Security Group];
```### Example
```sql
CREATE USER [FABRIC-Finance-Readers] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [FABRIC-Finance-Readers];
```### Notes
- The principal must be a **Microsoft Entra ID security group**.
- The name in brackets should match the Entra group name.
- If the group/user does not already exist in the warehouse, you need to create it first before adding it to the role.### Microsoft references
- `ALTER ROLE` syntax: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver17
- `CREATE USER ... FROM EXTERNAL PROVIDER`: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver17
- Fabric warehouse permissions overview: https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissionsHi Ramkishor ,
Since a security group doesn't have an email/UPN, you reference it by its Entra ID display name instead. The approach is the same one used for Azure SQL / Synapse: first create a database user mapped to the group, then add that user as a member of your role.Step 1: Create a database user for the group
CREATE USER [Your Security Group Display Name] FROM EXTERNAL PROVIDER;
Use the exact display name of the group as it appears in Entra ID, in square brackets - not an email/UPN, since groups don't have one. If you use the wrong name or an alias, you'll get an error like:
Msg 33130 ... Principal 'xxx' could not be found or this principal type is not supported.
Step 2: Add the group user as a member of your role
ALTER ROLE test
ADD MEMBER [Your Security Group Display Name];That's it - no need to add individual users anymore. Every user in that Entra security group now inherits whatever GRANT/REVOKE permissions you've set on "test".
Full example:
CREATE ROLE test;
CREATE USER [Sales Data Readers] FROM EXTERNAL PROVIDER;
ALTER ROLE test
ADD MEMBER [Sales Data Readers];GRANT SELECT
ON dbo.[********]
TO test;Note please:
Permissions to resolve the group - to use CREATE USER with FROM EXTERNAL PROVIDER, you must be a member of the Directory Readers role in Microsoft Entra. If the lookup still fails, it's usually because resolution actually happens via the workspace's managed identity, not your own permissions. The Warehouse needs its own Managed Identity enabled, granted the Directory Readers role in Entra ID, plus Microsoft Graph Directory.Read.All application permission with admin consent.
Name uniqueness - if two groups happen to share the same display name, CREATE USER [name] FROM EXTERNAL PROVIDER becomes ambiguous. In that case you'd need to resolve by object ID instead of display name (via Graph/PowerShell to confirm the correct object first).
Nested groups - Fabric/Entra does support nested group membership resolution for auth, but it's worth testing with a real user in the group after setup rather than assuming it works automatically for deeply nested groups.
You don't have to pre-create the user manually if you're managing roles through the Fabric portal's "Manage SQL Security" UI - the portal automatically creates users when needed. But since you're scripting this with T-SQL, doing the explicit CREATE USER ... FROM EXTERNAL PROVIDER step yourself is the correct approach.
5 Replies
powerbidev123Solution Sage
Hi Ramkishor
In a Microsoft Fabric Warehouse, you typically:
1. **Create a database user for the Microsoft Entra ID security group**
2. **Add that user to the database role**### Syntax
```sql
CREATE USER [My Entra Security Group] FROM EXTERNAL PROVIDER;
ALTER ROLE [MyDatabaseRole] ADD MEMBER [My Entra Security Group];
```### Example
```sql
CREATE USER [FABRIC-Finance-Readers] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [FABRIC-Finance-Readers];
```### Notes
- The principal must be a **Microsoft Entra ID security group**.
- The name in brackets should match the Entra group name.
- If the group/user does not already exist in the warehouse, you need to create it first before adding it to the role.### Microsoft references
- `ALTER ROLE` syntax: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver17
- `CREATE USER ... FROM EXTERNAL PROVIDER`: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver17
- Fabric warehouse permissions overview: https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions
v-kathullacCommunity Support
Thankyou powerbidev123 for Addressing the issue.
Hi Ramkishor ,
Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
- v-kathullac
Thankyou @powerbidev123 for Addressing the issue.
Hi @Ramkishor ,
Thank you for reaching out to Microsoft Fabric Community Forum,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya
Good news on this one, the group doesn't actually need an email address for it to work through T-SQL. That email requirement is only a thing when you're adding a group through the Fabric portal UI directly.
For your case, you can basically just swap in the group's display name where you'd normally put a user's UPN:
CREATE USER [YourSecurityGroupDisplayName] FROM EXTERNAL PROVIDER;
ALTER ROLE test
ADD MEMBER [YourSecurityGroupDisplayName];A couple of things worth knowing before you run this. Make sure you use the group's exact display name from Entra ID, not an alias, since groups without an email won't have one to use anyway, and trying to use one that doesn't exist just throws a "principal could not be found" error. You'll also need to be in the Directory Readers role in Entra ID, otherwise the lookup will fail.
One more thing: you don't strictly need that CREATE USER line first. ALTER ROLE will create the user for you if it isn't there already. It's just nice to run it separately so you can confirm the group actually resolves before you try adding it to the role.
Once it's in there, anyone added to that Entra group later automatically picks up the role's permissions, so you shouldn't have to touch SQL again for membership changes.
Hi Ramkishor ,
Since a security group doesn't have an email/UPN, you reference it by its Entra ID display name instead. The approach is the same one used for Azure SQL / Synapse: first create a database user mapped to the group, then add that user as a member of your role.Step 1: Create a database user for the group
CREATE USER [Your Security Group Display Name] FROM EXTERNAL PROVIDER;
Use the exact display name of the group as it appears in Entra ID, in square brackets - not an email/UPN, since groups don't have one. If you use the wrong name or an alias, you'll get an error like:
Msg 33130 ... Principal 'xxx' could not be found or this principal type is not supported.
Step 2: Add the group user as a member of your role
ALTER ROLE test
ADD MEMBER [Your Security Group Display Name];That's it - no need to add individual users anymore. Every user in that Entra security group now inherits whatever GRANT/REVOKE permissions you've set on "test".
Full example:
CREATE ROLE test;
CREATE USER [Sales Data Readers] FROM EXTERNAL PROVIDER;
ALTER ROLE test
ADD MEMBER [Sales Data Readers];GRANT SELECT
ON dbo.[********]
TO test;Note please:
Permissions to resolve the group - to use CREATE USER with FROM EXTERNAL PROVIDER, you must be a member of the Directory Readers role in Microsoft Entra. If the lookup still fails, it's usually because resolution actually happens via the workspace's managed identity, not your own permissions. The Warehouse needs its own Managed Identity enabled, granted the Directory Readers role in Entra ID, plus Microsoft Graph Directory.Read.All application permission with admin consent.
Name uniqueness - if two groups happen to share the same display name, CREATE USER [name] FROM EXTERNAL PROVIDER becomes ambiguous. In that case you'd need to resolve by object ID instead of display name (via Graph/PowerShell to confirm the correct object first).
Nested groups - Fabric/Entra does support nested group membership resolution for auth, but it's worth testing with a real user in the group after setup rather than assuming it works automatically for deeply nested groups.
You don't have to pre-create the user manually if you're managing roles through the Fabric portal's "Manage SQL Security" UI - the portal automatically creates users when needed. But since you're scripting this with T-SQL, doing the explicit CREATE USER ... FROM EXTERNAL PROVIDER step yourself is the correct approach.
