RLS using USERPRINCIPAL Name - for 50+ users - need to add them all individually?

[dancarr22]

Hello,

We are using RLS security by leveraging USERPRINCIPALNAME.  This works.  The only issue is there are many different users with different permissions.  Do we need to add everyone in PBI service to that secured role separately?  Online documentation states that groups do not work - and in our testing that seems to be the case.  Just hoping there is a workaround as the # of users could balloon to hundreds and a pain to have to add each of them individually to the role in PBI service.

Thanks,

Dan

[dancarr22]

Got it - thanks!  I was under the impression that we could not use groups when adding role users.  But, if we can do that - it makes sense.  Thanks - issue resolved.

[lbendlin]

If you use dynamic RLS then your data model needs to contain a mapping table between email addresses and capabilities/permissions. Ideally that table is maintained outside of Power BI.

[dancarr22]

Thanks @lbendlin  - I do have dynamic RLS set up.  The issue is we still have to go into the service security and add ever user individually to that role.  And any time a new user is given access - which works behinds the scenes because it is managed in our SQL database - we still need to set that new person up in PBI Service role security.  Unless I'm missing something.  

Seems like it would be much easier if we could ust add the USERPRINCIPALNAME filter directly to the SQL WHERE clause in the connection string - but that doesn't seem to be possible in M.  Doing so would prevent the need of having to add every person to the secure role in PBI service.

Thanks,
Dan

[lbendlin]

The issue is we still have to go into the service security and add ever user individually to that role. 

Not sure I understand that part. Usually all you need to do is refresh your semantic model to pull in the new user mapping information.

You can consider using Direct Query with SSO passthrough 

[dancarr22]

Maybe I misunderstood how RLS works but I thought that everyone who needs to be secured - in addition to being in the database - and filtered via RLS - they also need to be added to the role in PBI service for the given semantic model's security here:

ie. this is not the last step:

...still need everyone to be added individually to PBI service role.  Not great as we have 50 or so users so far and there will be many more in the future.  And cannot assign a group to a role - and have it work.

[lbendlin · Accepted solution]

In Dynamic RLS you usually have a single role. That role has ideally only distribution lists as members , usually PDLs  maintained by an external tool.