How to set row level security customer company code linked to login ID

[yus]

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?

[v-shex-msft]

Hi @yus,

I'd like to suggest you to take a look at below link about RLS with username:

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

[yus]

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	user01@contoso.com	2018/1/1	10
AAA	user01@contoso.com	2018/2/1	20
AAA	user02@contoso.com	2018/1/1	10
BBB	user03@oslo.com	2018/1/1	20
BBB	user04@oslo.com	2018/1/1	30
CCC	user05@contoso.com	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?

[v-shex-msft · Accepted solution]

Hi @yus,

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