Microsoft SQL Server 2012 Bible

Checking Permissions

The value of row-level security is actually allowing or blocking reads and writes. These procedures, functions, and triggers are examples of how to build row-level read/write validation.

The Security-Check Stored Procedure

The security-check stored procedure, p_SecurityCheck, is central to the row-based security system. It's designed to return a true or false for a security request for a person, an address, and a requested security level.

The procedure selects the security level of the person for the given location and then compares that value with the value of the requested security level. If the person's permission level is sufficient, then a 1 (indicating true) is returned; otherwise, a 0 (for false) is returned:

CREATE PROCEDURE p_SecurityCheck
 @PersonCode VARCHAR(15),
 @AddressCode VARCHAR(15),
 @SecurityLevel INT,
 @Approved BIT OUTPUT
AS 
SET NOCOUNT ON;
DECLARE @ActualLevel INT = 0;
SELECT @ActualLevel = s.SecurityLevel
 FROM dbo.Security AS s
  INNER JOIN Person.Person AS p
   ON s.PersonID = p.BusinessEntityID
  INNER JOIN Person.Address AS a
   ON s.AddressID = a.AddressID
 WHERE p.BusinessEntityID = @PersonCode 
  AND a.AddressID = @AddressCode;

IF  @ActualLevel < @SecurityLevel
 SET @Approved = CAST(0 AS bit);
ELSE 
 SET @Approved = CAST(1 AS bit);

RETURN 0;

The following batch calls the p_SecurityCheck procedure and uses the @OK local variable to capture the output parameter. When testing this from the script on the web, try several different values. Use the p_Security_Fetch ...