I85PREP
⌘ K
PL-300 study materials
PL-300 study resources

The role that restricted nobody

PL-300 · Put your knowledge to work in context.

All case studies

The role that restricted nobody

A published app serves 300 regional managers from one dataset with a dynamic RLS role. A RegionManagers table maps UserPrincipalName to Region, and the role filters DimRegion using that mapping. The workspace has 4 members, all in IT, and 40 viewers; the app has a single audience containing all 340 users. Three symptoms: a regional manager who was added as a workspace Member now sees every region; an executive assigned both the Region Managers role and an All Regions role sees only her own region; a manager added to the mapping table at 09:00 still sees only her old region at 11:00. The mapping table is imported from on-premises SQL and refreshed nightly at 02:00.

1. Explain each of the three symptoms.

Read the worked answer

(1) The manager sees everything. She is a workspace Member. Admins, members and contributors have edit rights on the dataset, and RLS is not applied to anyone with edit rights - roles are evaluated only for consumers. The DAX is fine; the permission is wrong. Fix: remove her from the workspace role and give her access through the app as a viewer. Keep IT in a workspace that holds no data for end users.

(2) The executive sees only her region. Roles are additive: a user in two roles gets the union of the row filters. The union equals her own region only if the All Regions role returns nothing for her. Typical causes: the role filters the mapping table on USERPRINCIPALNAME() and she has no row flagged All; or the filter sits on a table with no filter path to the fact (inactive relationship or wrong direction), so it contributes no rows. Prove it with Test as role using her UPN specifically. Better pattern: one role whose mapping table carries an All flag, evaluated as an IN over a CALCULATETABLE of permitted regions.

(3) The new manager still sees the old region. RLS is evaluated against the last imported copy of the mapping table. A row added at 09:00 is invisible until the next dataset refresh at 02:00. Options: trigger an on-demand refresh, refresh more often, or move the mapping table to DirectQuery so membership is read at query time - at the cost of a query per request and a gateway dependency. Have her sign out and back in as well; the app can cache tokens.

2. A contractor must build reports over this dataset and must not see HR data. Design the access.

Read the worked answer

Build permission is read permission. Anyone with build can create their own report and query any table and column the role allows, so anything forbidden must be prevented by the model, not by the report.

  • Do not add the contractor as a member, contributor or admin - that bypasses RLS entirely.
  • Put them in a separate workspace as a viewer with build permission on the dataset (or grant build through the app), and assign them an RLS role for their regions. Verify with Test as role against their UPN, not yours.
  • RLS filters rows, not objects. It cannot hide the HR table or a salary column. Use object-level security through XMLA / an external editor to remove HR from their role, or publish a second dataset that never contains HR.
  • If they need only a subset and no DAX against the full model, a copied dataset holding just the needed tables is easier to audit than a finely tuned role.
  • Re-test at every release: role membership, the security table refresh, and the app audience.

3. What would you check before the next release, and how do you keep this from regressing?

Read the worked answer
  1. Effective identity tests: Test as role for one user per region plus edge cases - a UPN listed twice, and a UPN with no mapping row at all. A user with no row must see nothing; confirm the role has no fallback that grants everything.
  2. Permission drift: review workspace membership quarterly. Every member is an RLS exception. If that number grows, the model is no longer the control plane.
  3. Refresh dependency: the security table must land before the business day, so alert on refresh failure. A failed refresh silently leaves yesterday's membership in force - the third symptom, repeating.
  4. Distribution: confirm the app audience contains the right security groups, and that the workspace is not also shared by direct report links. For 340 users the app should be the only path.
  5. Change control: RLS expressions are model content. Include them in the deployment pipeline's comparison and re-run the test matrix after every deployment, not once at go-live.

Record the answers to the test matrix alongside the release; an untested role is an assumption, not a control.