You have implemented Row Level Security to restrict users to view data accordingly but you want to create a dynamic Admin role that can be assigned to any user to view all data and at the same time, you do not want to assign Admin permission to the user.
There are scenarios where username or userprincipalname required to use to give access but to use USERPRINCIPALNAME()dynamically and come with the different possibility.
Scenario 1 : In any Organisation each employee belongs to different department and have access as per his designation. An Executive always have access to all data which is related to his employee and a Manager to his department but not to the data which belongs to Executive and similarly Lower Level employee will have access to his data only.
Scenario 2. A User exists with multiple users in the same row.(Additional Example how we can use the USERPRINCIPALNAME( ))
Having one version of the truth but limiting that version of the truth, depending on who logs in, creates personalised and consistent reporting. Finance and Accounting teams deal with high volumes of the types of sensitive information which require restrictions dependent on who logs in; this includes commissions, travel and expense reporting or dealing with the roll up of companies. This blog provides an overview of the problem and a step-by-step guide on building your first report.
There were already written few blog posts both about Row Level Security. I would like to add one more about this topic. I use this pattern for several years in SQL Server Analysis Services. Biggest advantage of this approach is, that you don’t have to fight with table relationships, which can be sometimes tricky to make work correctly.