RLS Example

USE Sandbox;
Go
/* This Script assumes it is being run as a sysadmin, or at least a db_owner */

CREATE USER Jane WITHOUT LOGIN;
CREATE USER John WITHOUT LOGIN;
CREATE USER Sarah WITHOUT LOGIN;
GO

CREATE TABLE dbo.SomeTable (SomeID int IDENTITY(1,1),
                            SomeUser sysname NOT NULL,
                            SomeOtherUser sysname NULL,
                            SomeString varchar(3) NULL,
                            SomeDate date);
GO
GRANT SELECT ON dbo.SomeTable TO Jane;
GRANT SELECT ON dbo.SomeTable TO John;
GRANT SELECT ON dbo.SomeTable TO Sarah;
GO
CREATE FUNCTION dbo.tvf_SomeTable_SecPred (@SomeUser sysname, @SomeOtherUser sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS SecPred
    WHERE USER_NAME() IN (@SomeUser,@SomeOtherUser)
       OR USER_NAME() = 'Jane'
       OR USER_NAME() = 'dbo';
GO

CREATE SECURITY POLICY SomeFilter
ADD FILTER PREDICATE dbo.tvf_SomeTable_SecPred(SomeUser,SomeOtherUser)
ON dbo.SomeTable
WITH (STATE = ON);
GO

INSERT INTO dbo.SomeTable (SomeUser,SomeOtherUser,SomeString,SomeDate)
VALUES('John','Sarah','abc',GETDATE()),
      ('Jim','Sarah','def',GETDATE()),
      ('Jane',NULL,'xyz',GETDATE()),
      ('Sarah','Jane',NULL,GETDATE());
GO

SELECT *
FROM dbo.SomeTable;
GO

CREATE PROC dbo.SomeProc @SomeUser sysname, @SomeOtherUser sysname = NULL AS
BEGIN
    SELECT SomeID,
           SomeUser,
           SomeOtherUser,
           SomeString,
           SomeDate
    FROM dbo.SomeTable
    WHERE SomeUser = @SomeUser
       OR SomeOtherUser = @SomeOtherUser;
END;
GO
GRANT EXECUTE ON dbo.SomeProc TO Jane;
GRANT EXECUTE ON dbo.SomeProc TO John;
GRANT EXECUTE ON dbo.SomeProc TO Sarah;
GO
SELECT * FROM dbo.SomeTable; --Returns all the rows, dbo is always allowed
EXEC dbo.SomeProc @SomeUser = 'Jim'; --Returns a row, dbo is allowed
EXEC dbo.SomeProc @SomeOtherUser = 'Sarah',@SomeUser = 'Jane'; --Returns 3 rows, dbo is allowed
GO

--Try as Jane;
EXECUTE AS USER = 'Jane';
GO
SELECT * FROM dbo.SomeTable; --Returns all the rows, Jane is always allowed
EXEC dbo.SomeProc @SomeUser = 'Jim'; --Returns a row, Jane always allowed
EXEC dbo.SomeProc @SomeOtherUser = 'Sarah',@SomeUser = 'Jane'; --Returns 3 rows, Jane always allowed
GO
REVERT;
GO
--Try as John
EXECUTE AS USER = 'John';
GO
SELECT * FROM dbo.SomeTable; --Returns only rows where John is SomeUser (1 row) (no rows where they are SomeOtherUser)
EXEC dbo.SomeProc @SomeUser = 'Jim'; --Returns no rows, John isn't Jim or Sarah (values of columns on the row)
EXEC dbo.SomeProc @SomeOtherUser = 'Sarah',@SomeUser = 'Jane'; --Returns 1 row, where John is SomeUser
GO
REVERT;
GO

--Try as Sarah
EXECUTE AS USER = 'Sarah';
GO
SELECT * FROM dbo.SomeTable; --Returns only rows where Sarah is SomeUser (1 row) or SomeOtherUser (2 rows)
EXEC dbo.SomeProc @SomeUser = 'Jim'; --Returns a row, Sarah is the OtherUser
EXEC dbo.SomeProc @SomeOtherUser = 'Sarah',@SomeUser = 'Jane'; --Returns 2 rows, Sarah is the User
GO
REVERT;


GO
DROP PROC dbo.SomeProc;
DROP SECURITY POLICY SomeFilter;
DROP TABLE dbo.SomeTable;
DROP FUNCTION dbo.tvf_SomeTable_SecPred;
DROP USER Jane;
DROP USER John;
DROP USER Sarah;