Row Level Security and Roles

[andrewnzinuk]

I have a table called Cost_Centre, within that table I have a column called UPN which is the UPN of the cost_Centre manager. This column can have multiple values in it. For example I have placed the following in the UPN colum :

james@abc.com

mary@abc.com

Cindy@abc.com; Mark@abc.com

harry@abc.com

If I login as Mark@abc.com - I want to only see rows that Mark can see . I have created a role and added a filter on the UPN column - and was hoping to restrict rows based on the UPN value in the column.

This works when I only have a single user in the UPN column - but sometimes I need more than one person :

When logged in as Mark this works with Mark@abc.com as the only value in the UPN column for the row :

=dim_CostCentre[UPN] = USERPRINCIPALNAME()

So I have tried this with the string with multiple users  - and cannot make it work :

IF(CONTAINS('dim_CostCentre',dim_CostCentre[UPN],USERPRINCIPALNAME())

When logged in as Mark @abc.com no rows are visible. I think the contains is working on the column and not the row. Any ideas on how I can make this work.

Thanks

[ppm1]

Try this instead for your RLS filter. If performance poor, you could alway split your text into a list with Text.Split and expand that list to new rows and use your simpler filter expression.

CONTAINSSTRING(dim_CostCentre[UPN], USERPRINCIPALNAME() )

Pat

[andrewnzinuk]

Hi Pat

Went back to CONTAINSSTRING and worked perfectly as far as I have tested it.

My issues was I was running on Analysis Services 2017 - upgraded to 2022 and it worked.

I think 2019 would work as well.

Cheers

[andrewnzinuk]

Hi Pat

Tried that - unfortunately my Visual Studio does not recognise that DAX function. I am not sure if it relates to my version of Analysis Services - which is SQL 2017. I was going to upgrade and see if CONTAINSSTRING will be accepted, is it a relatively new function . Thanks