topic Re: Is RLS what I need? in Report Server

Is RLS what I need?

philburns — Tue, 23 Jul 2019 08:44:07 GMT

I'm planning to close down a load of SQL driven .asp pages and replace them with Power BI reports. Currently I make sure that a user gets a unique dataset with a SQL query such as SELECT * FROM Table1 WHERE Field1=USERID.

How can I make sure that this functionality is replicated in power BI?

Is RLS what I need to use, or something else?

And would it be best to apply RLS to one query to select the USERID and then link that query with each of the various other queries which produce various datasets?

Thanks

Re: Is RLS what I need?

cbarrettEM — Tue, 23 Jul 2019 19:21:01 GMT

RLS will do what you need.

Setting up RLS in the editor is just a GUI for a query. It shows you the list of tables in your dataset, and you can filter on any combination of the fields once certain criteria are met, ie UserID. I think the one problem you may have is I believe you will have to set up a role, for every single one of your users (eg UserID = 'ABC' Filter table for 'ABC' ... UserID = DEF Filter table for 'DEF, etc). I may be wrong, but I believe the filter 'where clause' is static, so you cant build a one fits all dynamic role.

Re: Is RLS what I need?

philburns — Tue, 23 Jul 2019 20:15:15 GMT

Thanks for that.
I haven't come across role settings yet - where can I find out about that and where are they defined?

Re: Is RLS what I need?

d_gosbell — Tue, 23 Jul 2019 22:50:21 GMT

If you click on the Modelling tab in Power BI there is a manage roles button which brings up the following dialog box

In the role above users in the role will only see the one "SecureCostCentre" where it is equal to 1111 and this filter will flow down to any related tables.

This filter can be any valid DAX expression. In your case you could use the USERNAME() function in your filter to make a single dynamic role if you have a table in your model with a list of usernames that is then also related to the other tables in your model. So if you had a 'Users' table with a [Login] column you could create a filter expression like the following on your Users table.

Users[Login] = USERNAME()

Re: Is RLS what I need?

philburns — Tue, 23 Jul 2019 23:36:30 GMT

Wow thats fantastic, thank you all so much.
I'm going to have fun with this now!!!!