I finished creating a Power BI report and need to apply row-level security, however, I’m going to need to do this for at least 50 users. I know how to create this within the Power BI file itself by creating a role and after publishing, I’ll need to go to security and add people to those roles based on their email.
Is there an easy way to create row-level security roles based off some sort of Excel file that has a person’s name and their email? Or would I need to create 50+ individual roles for each person?
I usually connect to Azure Active Directory, bring in all users within the tenant, create a flags based on AD properties in Power Query, load it as a disconected and hidden table in the model and use that for defining more generic roles.
Also in my Azure Active Directory I’ve added a Dynamic User Group to identify all Power BI Pro users with these properties (note, you’ll need to verify the assignedPlan.servicePlanId):
But you can create as many dynamic groups as you need by defining, more dynamic membership rules based on AD properties. Instead of adding and maintaining 50+ individual emails, you can add a couple of dynamic groups…
Are these 50 users accessing data related to themselves or their teams or regions etc? If that is the case, you don’t have to create 50 roles, just 1 dynamic user role should suffice. However, for this to work, you need to add a user column to the Dimension table and assign email IDs of the individuals to the rows they have access to.
Also, create a O365 Group, Security Group or Distribution Group in AD and add all 50 users to this group. Assign this O365 Group, Security Group/Distribution Group to the Dataset or Report or Workspace you wish to share. You just have to add names just to this O365/Distribution/Security Groups rather than add 50 users to each of these places.
@Melissa@pranamg I found the Dynamic RLS approach to be what I needed - never knew we could do this! This is great because when using userprincipalname() it’ll capture the user’s email and as long as I have a table in the Power BI report containing email credentials.
I do have one issue though: if a user has Admin access to the workspace then does that allow them to bypass the RLS and therefore see all the data?
I tested this out with a user who had Admin access to the workspace and they could see data that pertained to other users. When I changed their access to Viewer then the dynamic RLS was successful and they could only see their information.
Hi @EverythingZen - Yes the above understanding is correct, if a User is part of the workspace in a role other than viewer i.e. Admin/Member/Contributor then he/she will have access to all the Data.
Instead of adding and maintaining 50+ individual emails, you can add a couple of dynamic groups…
