Problem with two dataset of same database but different user access

[joefreydc]

Lets say I have 2 workspaces (wsA and wsB) with 1 dataset each. Both datasets on workspace wsA and workspace wsB connects on the same sql server and same database. The database has 4 tables (tb1, tb2, tb3, tb4). The dataset from workspace a uses a user that has access in tables tb1 and tb2 while the dataset from workspace b uses a user that has access in tables tb3 and tb4.

Now the problem is when I update the datasource credentials of the dataset in wsA, the dataset in wsB get upset and could not refresh. The same goes vice versa.

How can I fix this?

Thanks in advance

[v-shex-msft · Accepted solution]

HI @joefreydc,

Current power bi not support dynamic tables based on credentials.

For your scenario, power query generation operation steps and cache table structure when your connection database with credentials 'user A'.

When you change credentials to 'user B', his permission not works on 'user A' tables. So he can't refresh cache tables from previous operation.(credentials invalid)

In my opinion, you can create different reports for different uses or consider to load all tables, then use RLS to control displayed records.

RLS with UserName()

BTW, object level security or other features which will generate dynamic table structure also not works on power bi side.

Regards,

Xiaoxin Sheng

[dpiret]

Hi there. Has not been there any solution to this since 2018?

I ran into the same problem: datasets created in Power BI Desktop connect to the database with the user/pwd provided, but when PBIX are uploaded to Power BI service, it uses whichever user connected first (cached).

The goal here is  to create different workspaces (single-teannt embedded) that direct-query connect to the same database (which is multi tenant) with diferent database users. This is because the database itselft already has RLS implemented, granting each user access to their tenant rows.

cheers

[joefreydc]

Thank you very much for your reply. For the mean time, I did what you said and create one username that has access to all tables. But I still need to create a solution with my previous setup due to some security reasons. Based on my observation, it seems like powerbi saves credential with unique instance "server + database" per user. Thus, the previous setup works if I set each dataset credentials with different powerbi users.