I want to implement SQL Server Row Level Security with lookup table. I followed this document and was able to get the basic function working.

But I want to use lookup tableas mentioned here but external table as mentioned here.

Following is the schema for Data & User (lookup) Table

RLS_Sellout ( external table ) : [ Id , Operator , Sales , ProviderId ]

RLS_User : [ Id , Email , ProviderId , TableName , isAuthorized ]

When user run a query on Data table, Filter Function should show only data for authorized ProviderId for the user, which is mapped in User table.

I tried to create function WITH SCHEMABINDING but it gave me error

The option 'SCHEMABINDING' is not supported with external tables.

But following function without schemabinding is working so far with email mapping.

CREATE FUNCTION FN_RLS_SellData
         (@ProviderID AS INT)
RETURNS TABLE
AS 
RETURN
(
    SELECT 1 as Result
    FROM [RLS_Sellout] d
    INNER JOIN [RLS_User] e ON e.ProviderID = d.ProviderID
    WHERE ( d.ProviderID = @ProviderID AND e.[Email] = System_USER )
);

I am using Users Authenticated username as Email for matching

I want to add TableName as a part of of the filter function, where it will check if user is authorized ( isAuthorized ) to access the table ( TableName ) and then perform the filtering.

Update Jul 28 : I managed to create function after answer provided by @ Aaron

CREATE FUNCTION dbo.FN_RLS_SellData
(
  @ProviderID int,
  @TableName  sysname
)
RETURNS TABLE
AS 
  RETURN
  (
    SELECT 1 as Result
      FROM dbo.RLS_Sellout AS d
      INNER JOIN dbo.RLS_User AS e 
      ON e.ProviderID      = d.ProviderID
      WHERE d.ProviderID   = @ProviderID 
        AND e.Email        = SYSTEM_USER
        AND e.TableName    = @TableName
        AND e.IsAuthorized = 1
  );

But following policy creation failed

CREATE SECURITY POLICY RLS_Sellout 
    ADD FILTER PREDICATE rls.FN_RLS_Sellout(ProviderID, TableName)
    ON [dbo].[RLS_Sellout]
    WITH (STATE = ON);

with error

Invalid column name 'TableName'. Cannot schema bind security policy 'RLS_Sellout'. 'rls.FN_RLS_Sellout' is not schema bound.

Would appreciate any help on this.