Row level security in SQL Server 2016 with a normalised schema SQL Server 2016 supports row level security, which is great. However all of the samples assume that the username is inside the table you wish to apply security to (see example below- from MSDN https://msdn.microsoft.com/en-us/library/dn765131.aspx).

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
GO

CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep) 
ON dbo.Sales
WITH (STATE = ON);


If I had a more normalised schema, my username would be in a separate table, using an ID as a foreign key. In this situation, how would I use row level security to check for a user name? Can I add a join to the filter predicate?
The function can have table access, just remember that this will add additional overhead to every single query.

CREATE FUNCTION Security.fn_securitypredicate(@SalesRepID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securitypredicate_result 
      FROM dbo.SalesReps AS sr
      WHERE sr.SalesRepID = @SalesRepID
       AND (sr.name = USER_NAME() OR sr.name = 'Manager');
GO