RLS Not Working Properly?

[Anonymous]

Hi all,

I created RLS in a report that I built.

Let's say I want to restrict the data based on project.

I created a role called RLS-ProjectX, which has definition of [Project] = "X", then deployed the report to Power BI Report Server (May 2019).

In the Report Server, I go to Manage > Row-Level Security > Add Member, and add user A to RLS-ProjectX role.

User A can only see project X-related data in the report, but everyone else cannot see anything (see the error message below).

I then created another role called RLS-ProjectAll, with no definition, then in the Report Server, I assigned Everyone to that role.

The report works again but User A can see everything (even though user A is still assigned to RLS-ProjectX role).

How can I overcome this issue?

Is it meant to work like that?

I deployed the report to Power BI Service, and it seems to behave differently.

As long as the user is not assigned to RLS-ProjectX role, the report still works for them (and no one is assigned to RLS-ProjectAll role).

Thanks in advance.

[d_gosbell]

---

@Anonymous wrote:

How can I overcome this issue?

Is it meant to work like that?

---

Yes it is meant to work like this. If User A is a member of both RLS-ProjectX and RLS-ProjectALL the list of available projects which the user can see is generated by unioning the list of projects from each role so UserA will see all projects. The solution is to not include User A in the membership for the RLS-ProjectALL role.

The permissions on the service are slightly different as you also have the overlay of the workspace permissions to think about.

[Anonymous]

---

@d_gosbell wrote:

---

---

Yes it is meant to work like this. If User A is a member of both RLS-ProjectX and RLS-ProjectALL the list of available projects which the user can see is generated by unioning the list of projects from each role so UserA will see all projects. The solution is to not include User A in the membership for the RLS-ProjectALL role.

---

Is there an easy way to assign everyone except User A in the RLS-ProjectALL group?

It's not practical to include users one by one..

[d_gosbell]

---

@Anonymous wrote:

---

---

Is there an easy way to assign everyone except User A in the RLS-ProjectALL group?

---

Not really. We usually use AD Groups in our role membership rather than adding individual users. But you still have the problem then of creating a group without that one person in it. You could also use Powershell or some other scripting approach to either populate a group or the role membership