RLS using Dates

[Boycie92]

Hi

I am hoping someone can help.

I am trying to implement row level security. I have a fact table, an employee table and a security table.

We have a scenario where we have created multiple reports, but managers are ONLY allowed to see their OWN Teams reports. So, we have created a ‘Landing Page’ with navigation buttons for managers to use. They can still access the reports for other teams but with RLS they can’t see the data.

We wanted to test a scenario were one employee has moved from Team A to Team B.  We wanted their previous manager to see all their data BEFORE they transferred and the new manager to see all their data AFTER they transferred.

So within the EmployeeTable we created a new row for any employees that transferred.

E.g

FactID                   EmpID                   RowID                   ManagerID         ManagerEmail Team       FromDate            ToDate

10                           70                           1                                              4              A             1/01/2017            31/01/2018

10                           70                           2                                              5              B             1/20/218              null

I am using the ManagerEmail = Username() function

The problem we are having is that both managers can see everything for that employee, as I can’t get it to focus on specify dates.  

Can anyone help?

Thanks in advance,

Boycie92

[v-frfei-msft]

Hi @Boycie92,

Did you enable the option "Apply security filter in both directions" on between your fact table and the date table.

Regards,

Frank

[Boycie92]

HI @v-frfei-msft,

I don't have a date table. I have activated it between my employee table and security table.

Thanks,

Boycie92