User changing role from A to B

So that requirement is possibly to achieve, but it will mean restructuring your model and data load routines. What you need to do is to implement surrogate keys (so that you can have different versions of each manager) and maintain effective dates for each "version" of a manager and employee.

What you would end up with is a table like the following. The first 3 rows are where "Manager A" managed Employees B & C, Then on 24-Mar he moved and started managing Employees D & F. If you insert fact records using the EmpID and then apply RLS to a column that identifies the employee (like the "Employee" column in the example below) then you should achieve the result you are after.

You just need to be very careful to test your data load routines very carefully, because when a manager moves you need to generate new "versions" of every employee underneath them.

Thanks, a lot that was a great relief.