Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
spowell
Frequent Visitor

Users with multiple security roles suddenly can't see data

Hello,

 

Our organization recently ran into a problem that seems to be coming out of the blue. We have a Power BI report connected to a SQL server that uses row-level security and security roles to manage which of our internal users can see which data. Each of the report's tables has bidirectional security filtering [EDIT: none of the tables have bidirectional security filtering], and as far as I know this has been the case since the beginning. Users are assigned to roles based on which accounts they work on, so all users have multiple security roles and that has also been the case since the beginning. Every security role filters every table in the report. This has worked up until today.

 

Now many of our users are getting an error that says, "The user belongs to multiple roles that have security filters, which isn't supported when one of the roles has filters affecting the table 'Table Name' with SecurityFilteringBehavior=Both relationships." Some users have different values in the 'Table Name' slot, which suggests this isn't just one table that got its security filter setting flipped for some reason.

 

Two users with admin access to the workspace where the report is published are having no issues and don't see any errors.

 

I am at a loss to figure out what is going on, since the report was last republished weeks ago and no settings have been deliberately changed in the meantime. But it may be some setting I'm overlooking. Can you think of any troubleshooting steps I can take? Are there workarounds for this? Every security role filters every table in the report (two dozen security roles at this point x 5 tables), and this worked previously, so not looking for solutions that will require the tables to self-filter by user or that require the report to be restructured (since that would require a lengthy IT approval process) except as an absolute last resort... I just want to figure out what changed so I can undo it.

 

Thanks for any help you can offer.

 

ETA additional information: I discovered a few more details talking to users seeing the errors. There are some visuals that are still working and some that do not. Which visuals have an error is consistent between users with different security roles, so my next step will be checking the formulas for each of the columns and measures that those visuals have in common to see if there is any additional filtering behavior in them that might be an issue. I'll check that and post an update here afterward.

2 ACCEPTED SOLUTIONS

It does appear to be a feature change.

 

I've built exactly the same model in Power Bi Desktop 2.99.862.0 64-bit (November 2021)

 

bcdobbs_0-1640163541450.png

Relationship isn't flagging as being limited in this version.

 

If I view as two roles it works fine:

bcdobbs_1-1640163598926.png

@AlexisOlson have you seen any documentation on this?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

spowell
Frequent Visitor

I wanted to return and describe how I solved this issue in a way that allowed us to continue using multiple security roles for users, rather than the cumbersome workaround of having to define a custom role for each user.

 

For each table that had a many-to-many relationship with the main dataset, I used a step in Power Query to make a list of the unique values of their shared columns (in our case, taking unique values from the main dataset only, because unmatched values in the lookup tables don't matter). For example, the "Client Labels" table was connected to the "Current Dataset" table by client names in a many-to-many relationship where "Current Dataset" filtered "Client Labels" but not the other way around, so I created a list of unique client names, sorted it for human readability, and added it to the data model as an intermediary table.

 

let
    Source = List.Distinct(#"Current Dataset"[Client]),
    #"Sorted Items" = List.Sort(Source,Order.Ascending)
in
    #"Sorted Items"

 

Then I connected the new intermediary table to the "Current Datasets" table in a many-to-one relationship which allowed two-way filtering, and to the "Client Labels" table in a many-to-one relationship with one-way filtering. (You can't have two-way filtering of the same table from two different relationships, so you will need to prioritize which of the tables controls filtering of the intermediary table and which is subject to filtering only.)

 

I did this for each of the many-to-many relationships in the data model, deactivated the old relationships, and activated the new ones. This fixed the problem completely.

 

One thing to keep in mind is that the "Allow security filter in both directions" box needs to be unchecked for the relationships with two-way filtering. If it gets checked you'll have the same RLS error pop up for users with multiple roles.

View solution in original post

25 REPLIES 25
gstoien-syk
Regular Visitor

My organization is suddenly experiencing this issue as well.

 

We haven't made any changes to our data models or RLS role setup and have had users assigned to multiple roles through memberships in email distribution lists (e.g. regional managers are in "RM" role and in "Sales Rep" role because they are members of both RM and Rep email alias/distribution list), but this has never caused an issue before...

 

This is impacting several of our production level reporting tools.

spowell
Frequent Visitor

I wanted to return and describe how I solved this issue in a way that allowed us to continue using multiple security roles for users, rather than the cumbersome workaround of having to define a custom role for each user.

 

For each table that had a many-to-many relationship with the main dataset, I used a step in Power Query to make a list of the unique values of their shared columns (in our case, taking unique values from the main dataset only, because unmatched values in the lookup tables don't matter). For example, the "Client Labels" table was connected to the "Current Dataset" table by client names in a many-to-many relationship where "Current Dataset" filtered "Client Labels" but not the other way around, so I created a list of unique client names, sorted it for human readability, and added it to the data model as an intermediary table.

 

let
    Source = List.Distinct(#"Current Dataset"[Client]),
    #"Sorted Items" = List.Sort(Source,Order.Ascending)
in
    #"Sorted Items"

 

Then I connected the new intermediary table to the "Current Datasets" table in a many-to-one relationship which allowed two-way filtering, and to the "Client Labels" table in a many-to-one relationship with one-way filtering. (You can't have two-way filtering of the same table from two different relationships, so you will need to prioritize which of the tables controls filtering of the intermediary table and which is subject to filtering only.)

 

I did this for each of the many-to-many relationships in the data model, deactivated the old relationships, and activated the new ones. This fixed the problem completely.

 

One thing to keep in mind is that the "Allow security filter in both directions" box needs to be unchecked for the relationships with two-way filtering. If it gets checked you'll have the same RLS error pop up for users with multiple roles.

This solution partially works, but in my testing the intermediary table is not actually being filtered by the RLS evalution tied to the "Current Datasets" equivalent in my model even with cross directional filtering set to "Both". The intermediary table is only filtered by RLS if "Allow security filter in both directions" is enabled, which, as you mentioned, causes the same error. Did you add another RLS formula to the intermediary table?

SilviaM
Frequent Visitor

Hi, I have the same issue with RLS, suddenly users can't see the data. My scenario is: I have many project IDs and a user needs to have access to 1 or more, I had roles by Project ID making the filter by Project ID = "345" and on RLS in web service I grant access to all users that need the information just for that ID.

After issue that users can't see data last Dec 2021 I tried to create a role as Juan Perez and his filter is [Project ID] IN {"234","456","874","768"}, on RLS in web service i just provide the access to Juan Perez by email and it worked for me! 

The big problem here is I have to change all my manage roles by user, positive thing here, i do not have a lof ot users!

Hopefuly this workaround helps you!

bjkoning-bince
Advocate I
Advocate I

We are experiencing the same issue. The Power BI service is throwing an "SecurityFilteringBehavior=Both" error, yet the model does not contain any after checking in Tabular Editor. It happens for multiple datasets that have the same users in different roles. Those assigned a single roles experience no issues.

 

For us this behavior is new and has worked until a week or so ago. I have not been able to resolve it other than having non-overlapping roles as a workaround.

spowell
Frequent Visitor

@bcdobbs Yes, it's in import mode and the RLS is defined in the dataset.

Really odd. Afraid I have no instant answer, we have a similar setup with users with multiple roles. Will do some reading!

A few initial thoughts:
1) Are you running PPU or premium capacity? You're clearly sure it hasn't been republished recently, just wondering if anyone could have changed something via an XLMA end point with tabular editor or VS?

 

2) Are you able to republish your pbix file to see if it starts working again?

 

3) If you click the view as role in the service does it work (Expecting yes and that will only simulate a single membership)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

1. Premium capacity. I'm not familiar with the other two methods you mentioned, and it seems very unlikely since we haven't used those features previously. There's only one other person likely to have changed the dataset and he has not done so.

 

2. Tried republishing, the users are still seeing the errors.

 

3. Yes, viewing as a single role looks fine.

 

I discovered a few more details talking to users that I will add to the main post, but there are some visuals that are still working. Which visuals have an error is consistent between users with different security roles, so my next step will be checking the formulas for each of the columns and measures that those visuals have in common to see if there is any additional filtering behavior in them that might be an issue. I'll check that and post an update here afterward.

Ok that's interesting. Are you able to recreate the issue in power bi desktop by selecting multiple roles. Is the behaviour the same if you select multiple roles in the service (https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls#validating-the-role-within-the-pow...).

 

On the visuals with the error can you provide the underlying query? (Click copy query) in performanxe

analyser https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

In Power BI desktop everything looks fine, including when multiple roles are selected. When viewing as certain users in the service, I see the same errors those users are reporting. (Also, thanks for that - I didn't know that feature existed.)

 

Here's the query from one of the visuals that works on desktop but doesn't work on the service:

 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({4}, 'Current Dataset'[Months])

  VAR __DS0FilterTable2 = 
    TREATAS({"Client Name"}, 'Current Dataset'[Client])

  VAR __DS0FilterTable3 = 
    TREATAS({DATE(2021, 10, 31)}, 'Current Dataset'[ScorecardValuation])

  VAR __DS0FilterTable4 = 
    TREATAS({DATE(2021, 10, 31)}, 'Client Labels'[ScoreCardValuation])

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    __DS0FilterTable2,
    __DS0FilterTable3,
    __DS0FilterTable4,
    "v_Policy_Fiscal_Calendar_Year", IGNORE('Current Dataset'[&Policy Fiscal Calendar Year]),
    "MaxScorecardYears", IGNORE(CALCULATE(MAX('Current Dataset'[ScorecardYears])))
  )

 

The client name, date, and months are selected by the user from slicers, and so far what I have been able to determine is that the table Client Labels is causing problems for anything referencing it, as well as another table, Actuarial2. [&Policy Fiscal Calendar Year] is a measure in the Current Dataset table that checks the Client Labels table to select the correct year label based on the client's name.

 

Here's one that relies only on Actuarial2, not Client Labels:

 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Client Name"}, 'Current Dataset'[Client])

  VAR __DS0FilterTable2 = 
    TREATAS({DATE(2021, 10, 31)}, 'Current Dataset'[ScorecardValuation])

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    __DS0FilterTable2,
    "Actuarial_valuation_title", IGNORE('Current Dataset'[Actuarial valuation title])
  )

 

 [Actuarial valuation title] is a measure in the Current Dataset table, which is fine, that checks the Actuarial2 table to see what date it's valued as of, in order to create a title on the report.

 

Hi,

I have a subscription to sqlbi.com's mastering tabular videos. I've been back to the security section to brush up my knowledge. It states very clearly that the error you're getting is caused because propagate security filter in both directions is not supported with multiple roles. (Currently trying to find mention of this in Microsoft documentation).

 

Their proposed solution is to very rarely tick that box and do the work in dax instead. For example if you want to count products with sales instead of writing COUNTROWS(Product) you can use DISTINCTCOUNT( Sales[ProductID).

 

What that didn't answer is why it appears to have worked historically. Have you recently had a drive to encourage greater engagement with power bi: eg is is possible the issue has always been there but people are only just discovering it?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi bcdobbs, I wanted to come back and add new information, since I think I was misunderstanding something before.

 

This is a screenshot of what the relationship settings look like for the Client Labels and Current Dataset table. I thought they had bidirectional filters, but they actually just have a many-to-many relationship. None of the tables in this report have bidirectional security filters turned on. (I apologize for stating otherwise earlier--I didn't build this report, so wasn't as familiar with it as I thought.)

 

spowell_0-1640126187832.png

Do you know of anything that could be causing the error message in the original post to show up when security both ways *isn't* turned on for any tables?

My hunch is that it is the same thing just under the hood.

 

Prior to Many-Many cardinality relationships you would build a bridge table which had a distinct list of values and relate them as follows:

A *<=>1 Bridge 1 =>* C

Notice that in order to propagate relationship from A into Bridge table the relationship is bidirectional.

 

With the advent of Many-Many relationship that same model is just happening behind the scenes when you do:

 

A * => * C

 

Therefore I suspect there is an implicit propegation of security filters in both directions.

 

Stretching a little here as don't think it should be a thing but I did wonder if there has been a change in the cardinality of underlying data that had caused things to break. Eg have you just rolled over into a second year of data or something like that?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Again haven't got a totally clear explanation in my head but is it possible that a recent data refresh introduced blank values on one or both sides of the relationships causing issues?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Not new ones. There are blanks in one of the year columns used as a key between the Actuarial table and Current Dataset, but that has been the case for a while and is designed to keep those rows from showing up in certain calculations. I guess I could try setting them to some other value and excluding them manually.

 

Thanks for your continued help. I get the feeling we're probably going to have to go with changing the security roles to work around this, but I appreciate the effort you've put into this.

I'll do a bit of testing later this morning to see if I can recreate a transient issue with many-many.

 

Until then 

 

1) Is it possible to tweak data model to avoid many to many by building an intermediate dimension table (use DISTINCT (table[keyname]). If you use that in visuals you can strip out the bidirectional aspect.

 

2) Is the security filter running on that table for security reasons or to tidy things up? If the later there might be other options. For example hiding options in slicers can be done with a measure:

INT ( NOT ( ISEMPTY ( FactTable) ) ) set to "Is 1" on the visual level filter of things like slicers.

 

Security can then just be applied to a couple of key dimension that need it rather than all.

 

3) Obviously if less work you're right that building security to be 1 role is a good option.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi again @spowell,

Did you say that you can't recreate the issue using view as role (clicking more than one) in power bi desktop? If so what version are you using? I'm wondering if there has been a feature change in the latest version/service. 

 

In the demo I've just built the many to many relationship now displays as limited (which it always would have been):

bcdobbs_0-1640159910087.png

I've just not seen it flagged in the diagram before apart from when using composite models which prompted me to ask question.

I can also recreate the error in power bi desktop with the above:

bcdobbs_1-1640160037562.png


Replacing the many to many relationship with the following (bidirectional security off) and moving security filter to State table lets everything work. (The state table can be hidden and not used anywhere).

bcdobbs_2-1640160397486.png

 





Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

It does appear to be a feature change.

 

I've built exactly the same model in Power Bi Desktop 2.99.862.0 64-bit (November 2021)

 

bcdobbs_0-1640163541450.png

Relationship isn't flagging as being limited in this version.

 

If I view as two roles it works fine:

bcdobbs_1-1640163598926.png

@AlexisOlson have you seen any documentation on this?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi @bcdobbs,

 

We have recently discovered (it appeared in December) exactly the same problem in our organisation where we use premium capacities to publish our PBI Apps.


On Power BI Desktop, the issue is reproducible only with the December version Version: 2.100.785.0 64-bit (décembre 2021)


For my point of view it is actually a regression of the datamodel behavior in power BI and Microsoft team should communicate on this pb because a lot of our reports are impacted and we the workaround is not very easy to implement.

 

Let me describe how to reproduce it with a simple data model :
Table A *=>* Table B with the Shipment number

 

Here is the M code : 

// Table A
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddI5jgJBDAXQu3RM4H0JBzjA5C3uf40pcCG1R1UhevrVxt/nefwct4MRMBGVYvz4BcDjdbsIM6GvxMNBlhm1JGmZewm9UzkzfBWUIEhYSZoE20pUNF1XEqhOWSL/JkjgmdGPPL7fCQybE3gTH3+Hp0QTAxaSkuyvMTDSRxCaxJhZ6jXE/h0RjnptRN/y/MoYCWaGm2SKUe0apUmkqk/RJkYhUhtF65mAsYUS76KEwiXRL+RyO7S9HdreDm1vh7ad2rZT33U6N7rodG500enc6KpT3XZq207HbK8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BU = _t, #"Shipment number" = _t, #"Picking location" = _t])
in
Source

// Table B
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBDsMwCATAv+ScAyxg4C1R/v+NulIveCv5tMJrPM9zmYq2aqCu+9LrvX+RGTRHlJXicypWw8cUvnM9p7wgLbN+edmaXR6dMaLSSPRR32Jzqktrzfrca9mMlpjDjz+KKeaLewU/utyt5sUU2Wd2tS/k0RWRM1oo9/ntKtn7zyigbv/twfZge7A92B5sD7YH24PtwfZge7A92B5sD7YH24PtwfZge7D9jt4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Shipment number" = _t, Item = _t])
in
Source

 

3 RLS are created : 

RLS A: [BU] = "A"

RLS B: [BU] = "B"
RLS A or B: [BU] = "A" || [BU] = "B"

 

When i simulate the display with "RLS A" and "RLS B" ticked, the visual is in error.
When i simulate the display with "RLS A or B", it is OK and the visual is displayed correctly.


I do not have the tool to capture the sql statement used in the 2 differents usecases, but i think it would be very helpful to prove there is a regression.
Bruno

Microsoft are aware of the issue. See the post from @v-jayw-msft in Dashboard with multiple RLS roles work in My Workspace but fails in Premium workspace 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.