Microsoft SQL Server 2012 Bible

Assigning Permissions

Implementing row-level security requires a set of basic admin procedures to set up and maintain the security settings. These procedures handle assigning security levels to users.

Assigning Security

For the Security table to be viewed, the first procedure created is pSecurity_Fetch. This procedure returns all the row-based security permissions, or it can be restricted to return those permissions for a single person or a single address:

CREATE PROCEDURE pSecurity_Fetch
 @AddressCode VARCHAR(15) = NULL,
 @PersonCode VARCHAR(15) = NULL 
AS 
SET NOCOUNT ON;
SELECT p.BusinessEntityID, 
a.AddressID, 
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 (a.AddressID = @AddressCode
            OR @AddressCode IS NULL)
      AND (p.BusinessEntityID = @PersonCode
            OR @PersonCode IS NULL);

Adding or altering rows in the Security table, which serves as a junction between person and location, in keeping with the theme of server-side code, the pSecurity_Assign stored procedure assigns a security level to the person/address combination. There's nothing new about this procedure. It accepts a person code and address code, and then performs the insert:

CREATE PROCEDURE pSecurity_Assign @PersonCode VARCHAR(15), @AddressCode VARCHAR(15), @SecurityLevel INT AS SET NOCOUNT ON; DECLARE @PersonID int, @AddressID int; -- Get PersonID SELECT @PersonID = BusinessEntityID FROM Person.Person WHERE ...