Row-level security (RLS) is easy to demonstrate and surprisingly hard to operate. The first version usually works. The trouble starts six months later, when people change roles, names are spelled three different ways and a new report quietly exposes a page nobody secured.

These are the patterns we rely on when sensitive data—compensation, patient-related financials, personnel information—has to reach hundreds of people through a single model.

Key access to identity, not to names

The only value Power BI reliably knows about a viewer is their sign-in identity. Make that the key. Store each person’s Microsoft Entra ID user principal name in a mapping table and filter on USERPRINCIPALNAME().

Then resolve business identities to that sign-in identity using stable identifiers. In healthcare, for example, a national provider identifier is far more dependable than a physician’s name, which may be spelled differently in scheduling, billing and payroll systems. Names belong in the report, not in the security rule.

Keep the rules in data, not in the model

Avoid a role per region or per person. A small number of table-driven roles scales much better:

  • All data for executives and finance.
  • Scoped access, where a mapping table lists the regions, sites or teams each person may see.
  • Individual access, where people see only rows tied to their own identity.

With this design, granting or removing access is a data change that can be reviewed and audited—not a model change that needs a release. Assign role membership through security groups owned by your identity team, so access follows your joiner, mover and leaver process.

Audit coverage before every release

Build a simple coverage view that answers three questions: who has a role but no mapping (and therefore sees nothing), who maps to more than one person, and who appears in the data but cannot sign in. Review it before each release and after every organizational change. It turns “why can’t I see my numbers?” tickets into a list you clear proactively.

Test like an attacker

For each persona, view the report as that role and as a specific user, and look for edge cases: someone who belongs to two groups, an inactive provider, a renamed employee, a brand-new hire. Confirm that totals, drill-through pages and tooltips respect the same rules as the main visuals.

Validate every membership change by querying as the user rather than trusting an API response or an admin screen. What matters is the effective identity the model actually applies.

Remember the side doors

Security is only as strong as its weakest path. Disable export in reports with sensitive detail, and if finance needs extracts, provide a separate, purpose-built export restricted to approved users. Check that personal workspaces, subscriptions and shared copies do not bypass the model you secured.

Design it once, operate it for years

Identity-keyed, table-driven security with a coverage audit takes slightly longer to build than a hard-coded list. It pays that time back within months—in fewer tickets, faster onboarding and the confidence to put sensitive data in front of the people who need it.