Row Level Security w/SecurityTable

[Anonymous]

Hello Power BI Masters! 

I have a question about Power BI RLS. I am struggling to find a working solution to provide security around our data using RLS in the Service. Currently, we use Power BI Desktop, on-premise. We have our security setup through sql views. First our ETL dump the data into our DW. Then, I create access layer DataMarts. Within the datamarts is where I implement our security. I use sql views to join and dim tables to my fact tables. The way we restrict user data, is I have a security table that is setup as follows:

The entity is the name of the DataMart I have setup. The rest is self explanatory. 

Also in the views, I join to this table for each of these values. Works perfectly for on-prem. I'm struggling to get it working in the cloud. Could I still use this model in the cloud? Or will I have to make my data model a little more complex for use in the cloud?

Let me know if I need to provide additional data and information. 

Thanks!  

[Anonymous]

Anyone? lol 

[mkoch]

How does the condition in your RLS role look like?
I assume you use the USERNAME() function.
Try creating 2 measures with like "username = USERNAME()" and "up = USERPRINCIPLENAME()".
See what these measures return in power BI Desktop and in the cloud.
I guess u need to add a column "userprinciplename" to your security table and use the email address instead. then update the role to use the new column and userprinciplename () function. This may not work in power bi desktop depending on your alternative upn settings of your local AD account.