Forum Discussion
Row-Level Security and AD groups
I've now successfully set this up.
I have 2 AD groups, both contain all users in the organisation. This means I don't need to assign permissions to individuals on the report server, I just add the 2 AD groups to one role each in the RLS security page.
One role is based on a hierarchy built from managers and their direct and indirect reports and the other role is based on departments within the organisation, e.g. where the user isn't a manager but needs access to data from a specific department.
These 2 roles have dax filters looking up the USERPRINCIPALNAME and matching it to data in separate datasets. If a user is in one of the datasets they will see data accordingly and if they are in both they will see the appropriate data from both.
You have three roles - that is not something you usually do in dynamic RLS (there you have only one role, and access is controlled via the data model). Of course you can implement a hybrid version but that gets messy quickly.
Yes I was thinking this. The problem is I have a default role which allows users to see their direct and indirect reports. This is based on relationships in a table that has the manager for each staff member (this is the dynamic RLS part I think)
Then there are some people who need access to one area of the organisation but they are not managers of that area (mostly admin staff or HR), so I created a separate table with these permissions based on the organisation hierarchy.