Forum Discussion
How to set row level security customer company code linked to login ID
Environment is Power BI embedded and Azure SQL
One table has all customer company data contain customer company code and users will access common Power BI Report. Login ID does not contain company code.
I want to show to user there company data that is match customer company code linked to login ID.
What should I do?
Hi Anonymous,
For your scenario, you can set up your RLS fitler on company_code filed.
Lookup company_code based on current username, then use this as filter parameter to apply on company_code field.
Sample formula:
[company_code] = LOOKUPVALUE ( 'Table'[company_code], 'Table'[login_Id], USERPRINCIPALNAME () )
Regards,
Xiaoxin Sheng
3 Replies
Hi Anonymous,
I'd like to suggest you to take a look at below link about RLS with username:
Add company code to user table to link company id with username and use this column to create relationship to other tables. Then you can use logined users to achieve dynamic rls filter.
Regards,
Xiaoxin Sheng
Hi Xiaoxin.
Thank you for reply. I tried to use RLS username() or userprincipalname() but the requeirments are racking my brain...
Table has data like below.
company_code login_Id date data AAA [email protected] 2018/1/1 10 AAA [email protected] 2018/2/1 20 AAA [email protected] 2018/1/1 10 BBB [email protected] 2018/1/1 20 BBB [email protected] 2018/1/1 30 CCC 2018/2/1 40 Requirements.
-Customer company has multiple user. "user01" and "user02" can see data linked "AAA".
-Some users have same domain but other company like subsidiary. So, "user01" and "user02" do not see data linked "CCC". "user05" can see only data linked "CCC".
-Domain does not necessarily include a company name or code.
So, I might be able to solve this matter if I could use customer company code linked to loginID for condition.
Would you have any good ideas?
Hi Anonymous,
For your scenario, you can set up your RLS fitler on company_code filed.
Lookup company_code based on current username, then use this as filter parameter to apply on company_code field.
Sample formula:
[company_code] = LOOKUPVALUE ( 'Table'[company_code], 'Table'[login_Id], USERPRINCIPALNAME () )
Regards,
Xiaoxin Sheng