Microsoft® SQL Server® 2012 Bible

873

Chapter 35: Row-Level Security

35

INSERT dbo.PersonLogin (PersonID, NTLogin)

SELECT BusinessEntityID, 'SHARK\David'

FROM Person.Person

WHERE BusinessEntityID = 118;

Check the data:

SELECT p.BusinessEntityID, cl.NTLogin

FROM Person.Person AS p

INNER JOIN PersonLogin AS pl

ON p.BusinessEntityID = pl.PersonID;

Result:

PersonCode NTLogin

--------------- --------------

118 SHARK\David

The new function you are creating is to join the PersonLogin table and to restrict the

rows returned to those that match the Windows login name. Because the person code is

no longer required, this select can skip the person table and join the Security table

directly with the PersonLogin table:

CREATE FUNCTION dbo.fSecurityCheckNT (

@AddressCode VARCHAR(15),

@SecurityLevel INT)

RETURNS BIT

AS

BEGIN;

DECLARE @Approved BIT = CAST(0 AS bit);

IF (SELECT s.SecurityLevel

FROM dbo.Security AS s

INNER JOIN Person.Address AS a

ON s.AddressID = a.AddressID

INNER JOIN dbo.PersonLogin AS pl

ON s.PersonID = pl.PersonID

WHERE pl.NTLogin = suser_sname()

AND a.AddressID = @AddressCode) >= @SecurityLevel

BEGIN;

SET @Approved = CAST(1 AS bit);

END;

RETURN @Approved;

END;

To test the new function, the following code fragment repeats the security check performed

in the last section, but this time the user will be captured from the Windows login instead

of being passed to the function:

c35.indd 873c35.indd 873 7/31/2012 9:59:41 AM7/31/2012 9:59:41 AM

http://www.it-ebooks.info