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?
Best Answer
The function can have table access, just remember that this will add additional overhead to every single query.