title: How to use Row-Level Security to weed out deleted records
body: <p>We're looking for help with an issue that I hope can be solved with row-level security.  We have 160 tables feed by a data lake that are in turn used by another application. There is a field in all tables called <em>IsDeleted</em> that is 0 if the record is good and 1 if deleted. What we need is for any user except the system application called <em>ETLapp</em>, they should only see records where <em>IsDeleted</em>=0.</p>
  <p>Using the Microsoft examples,
  CREATE users</p>
  <pre><code>CREATE USER mylogin WITHOUT LOGIN;
  CREATE USER yourlogin WITHOUT LOGIN;
  CREATE USER ETLapp WITHOUT LOGIN;
  GO
  </code></pre>
  <p>Then create table</p>
  <pre><code>CREATE SCHEMA SALES
  GO
  CREATE TABLE SALES.Orders
  (OrderID int,
  SalesRep nvarchar(50),
  Product nvarchar(50),
  Quantity smallint,
  IsDeleted bit
  );
  </code></pre>
  <p>Populate the Table</p>
  <pre><code>INSERT INTO SALES.Orders VALUES (1, 'Fred', 'Valve', 5, 0);
  INSERT INTO SALES.Orders VALUES (2, 'Daphne', 'Wheel', 2, 0);
  INSERT INTO SALES.Orders VALUES (3, 'Scooby', 'Pizza', 1, 1);
  SELECT * from SALES.Orders;
  </code></pre>
  <p>Grant Access</p>
  <pre><code>GRANT SELECT ON Sales.Orders TO mylogin;
  GRANT SELECT ON Sales.Orders to yourlogin;
  GRANT SELECT ON Sales.Orders to ETLapp;
  </code></pre>
  <p>Here's the part I can't make work right.</p>
  <pre><code>CREATE FUNCTION rls.SalesPredicate(@UserName as sysname)
  RETURNS TABLE
  WITH SCHEMABINDING
  AS RETURN SELECT 1 AS SalesPredicate_result
  WHERE IsDeleted =0 OR UserName = 'ETLapp';
  GO
  </code></pre>
  <p>It executes okay, but when I create the security policy, it won't accept username because username is not in the table, and if I pass it IsDeleted then we get all the rows.</p>
  <pre><code>CREATE SECURITY POLICY SalesFilter 
  ADD FILTER PREDICATE rls.SalesPredicate(UserName)
  ON Sales.Orders
  WITH (STATE=ON);
  GO
  </code></pre>
  <p>user ETLapp should see all 3 records, but anyone else should only see the two where IsDeleted=0.</p>