topic RLS upside-down in Report Server

RLS upside-down

Maria_Maria — Sun, 20 Mar 2022 18:47:53 GMT

Hi!

I need a way to allow only one user out of many to see all the data, and all other users to see the restricted data.

I can't do a regular group as there are many users and they are added every day, so I basically need an opposite to a regular RLS: all users except one have limited access.

Appreciate your help!

Re: RLS upside-down

Greg_Deckler — Sun, 20 Mar 2022 20:02:28 GMT

Maria_Maria Just create your RLS role for your one/few users. The other users will not have RLS applied if they are not assigned to an RLS role. You can prove this to yourself using the View as | Other user

Re: RLS upside-down

Maria_Maria — Mon, 21 Mar 2022 06:21:05 GMT

hi Greg_Deckler ,

This way 1 user will see the data based on his/her role, but the others will see all the data. What I need is exactly the opposite.

Re: RLS upside-down

Greg_Deckler — Mon, 21 Mar 2022 16:39:25 GMT

Maria_Maria Would need to understand your data to be more specific. But, in theory, create an Enter Data query with your users' UPN's that need to see all the data. Create an RLS rule like counting the rows in that table that match the UPN. If > 0 then return "true" to see all data, otherwise implement your RLS restriction. It's really kind of impossible to be more specific without knowing your data model and what you are trying to restrict.

Re: RLS upside-down

d_gosbell — Tue, 22 Mar 2022 05:28:41 GMT

Assuming you have an existing RLS role that is working the way you want and all you need to do is to give one of your users access to all the data. Then all you would need to do is to create a second role in your model with no restrictions or filters then add the user to this role. The permissions in roles are unioned together so this should grant unlimited access even if the user is also in the RLS role.

Re: RLS upside-down

Anonymous — Wed, 23 Mar 2022 07:54:07 GMT

Hi Maria_Maria ,

Please correct me if I have misunderstood.
You are now expecting one person in this organization to see all the content you want him to see all the time, while other users can only see limited content.
As a simple example, let's say you want to complete a manager-level RLS where your manager role users can see all the data all the time, and the people managed by the manager can only see the parts you want them to see, even if they are new to the organization and follow this rule.
If this is what you want, you can refer to this article below to set up an RLS.

Dynamic Row Level Security with Manager Level Access in Power BI

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: RLS upside-down

Anonymous — Wed, 23 Mar 2022 21:24:12 GMT

The simple answer is you create 2 roles

One with your RLS and that is given to everyone

then the second as an empty one (As in create it and do nothing), this second one is what you assign to the user that need everything.

Done 🙂

That simple