Forum Discussion

andrewnzinuk's avatar
andrewnzinuk
Frequent Visitor
3 years ago

Row Level Security and Roles

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 :

 

[email protected]

[email protected]

[email protected]; [email protected]

[email protected]

 

If I login as [email protected] - 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 [email protected] 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

 

 

 

 

 

3 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

    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's avatar
      andrewnzinuk
      Frequent Visitor

      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

    • andrewnzinuk's avatar
      andrewnzinuk
      Frequent Visitor

      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