1

I have three tables MST_EMployee, MST_ProfitCenter and Mapping_Employee_ProfitCenter. I wanted to set up row-level security on the Mapping_Employee_ProfitCenter table.

Steps which I am performing to achieve:

Step 1: create a table-valued function:

CREATE FUNCTION [RLS].[fn_CanSeeMapping] ( @UserName AS sysname )
RETURNS TABLE
WITH SCHEMABINDING 
AS
   RETURN
        (SELECT 1 AS 'CanSee' 
         WHERE @UserName IN (SELECT emp.ATTRFirstName  
                             FROM dbo.MAPPING_Employee_ProfitCenter map 
                             INNER JOIN dbo.MST_Employee emp ON map.FK_Employee_ID = emp.Id
                             WHERE emp.ATTRFirstName = USER_NAME())

Step 2: Security Policy created and added filter on mapping table and passing username as parameter whose id is present in mapping table.

CREATE SECURITY POLICY [dbo].[Security_Policy] 
ADD FILTER PREDICATE [RLS].[fn_CanSeeMapping]([FK_Employee_ID]) ON  [dbo].[MAPPING_Employee_ProfitCenter] 

ALTER SECURITY POLICY [dbo].[Security_Policy] WITH (STATE = ON)

Now when I am executing below query instead of giving record for user "User1" it is giving for all users.

EXEC ('select * from MAPPING_Employee_ProfitCenter') as user='User1'

If anyone knows how to handle this scenario please help me out.

Thanks!

Prayag15
  • 393
  • 3
  • 13

1 Answers1

0

Try following, which should be appropriate syntax :

EXECUTE AS USER = 'User1';  
select * from MAPPING_Employee_ProfitCenter;   
REVERT;
Mladen Oršolić
  • 1,352
  • 3
  • 23
  • 43