Extract RLS roles and users assigned

[Nishantjain]

Hi, 

I have multiple reports with all of them having RLS roles. Is there a way to extract the list of roles  and the users assigned to each one of them? 

For example, in one report I have 15 RLS roles and about 100 users assigned. It is very difficult to manage which user is assigned which role. 

Any help is appreciated

Thanks

Nishant

[GilbertQ · Accepted solution]

Hi there

What I would suggest is to use Dynamic Row Level Security and then use Active Directory Security Groups.

This will mean that you can have one role, and have the users in specific AD Security Groups. Which will make the administration, as well as who can see what a lot easier.

Here is my blog post explaining how to achieve this: https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security...

[Nishantjain]

Thanks  @GilbertQ for your response. I will try to give this a go. Do you know how to setup AD security groups. I believe we are restricted by the organisation policy to create security group. Given the number of roles that we have in our report, setting those many security group doesn't seem to be viable option

[GilbertQ]

Hi there,

Essentially you could set up on AD Security Group, which would have all the users.

You could then have another file which would have the mapping for each user and what data they need to see.
That could then be consolidated in Power Query.

Then in Power BI Desktop you would create only 1 role, and apply the DAX so that each user would only see their data.

Once this has been uploaded to the Power BI Service, you would then add this single AD Security Group under Security.

So there are a few steps but this would possibly the easiest way to get it working.