--Create Example data



CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[Description] [varchar](50) NULL,
	[Quantity] [int] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
	[OrderID] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
GO
INSERT [dbo].[Orders] ([OrderID], [UserID], [Description], [Quantity]) VALUES (1, 5, N'Apples', 5)
GO
INSERT [dbo].[Orders] ([OrderID], [UserID], [Description], [Quantity]) VALUES (2, 5, N'Oranges', 8)
GO
INSERT [dbo].[Orders] ([OrderID], [UserID], [Description], [Quantity]) VALUES (3, 7, N'Pears', 2)
GO
INSERT [dbo].[Orders] ([OrderID], [UserID], [Description], [Quantity]) VALUES (4, 7, N'Oranges', 7)
GO
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO


--Add a login and user


CREATE LOGIN db_user
    WITH PASSWORD = '&!pAssword';

CREATE USER db_user FOR LOGIN db_user WITH DEFAULT_SCHEMA=[dbo]

ALTER ROLE db_datareader
  ADD MEMBER db_user


GO
--Create a function to filter the user id with

create function dbo.fn_securitypredicate(@UserID int)
returns table
with schemabinding
AS
return
Select 2 AS fn_securitypredicate_result
Where DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('db_user')
and Convert(int, Session_Context(N'UserID')) = @UserID


GO

--Apply the filter to the table - multiple tables can use the same filter

CREATE SECURITY POLICY dbo.OrderSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(UserID) ON dbo.Orders

--Test the filter

EXECUTE AS USER = 'db_user'
--this would be set at login by your application
EXEC sp_set_session_context @key = N'UserID', @value = 5

--This will return 2
Select * from Orders where userid = 5
--This will return 0
Select * from Orders where userid = 7

GO

EXECUTE AS USER = 'db_user'
EXEC sp_set_session_context @key = N'UserID', @value = 7
--This will return 0
Select * from Orders where userid = 5
--This will return 2
Select * from Orders where userid = 7