Microsoft® SQL Server® 2012 Bible

865

Chapter 35: Row-Level Security

35

('Address: ''%s'' not found', 15,1,@AddressCode);

RETURN -100;

END;

-- Insert

INSERT dbo.Security (PersonID,AddressID, SecurityLevel)

VALUES (@PersonID, @AddressID, @SecurityLevel);

IF @@ERROR <> 0 RETURN -100;

RETURN;

With the pSecurity_Fetch and pSecurity_Assign stored procedures created, the

following batch adds some test data. The fi rst two queries return some valid data for the

test:

SELECT BusinessEntityID

FROM [AdventureWorks2012].Person.Person

WHERE PersonType IN ('EM','SP')

Result:

BusinessEntityID

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

1

2

The next query returns valid locations:

SELECT AddressID FROM Person.Address ;

Result:

AddressID

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

33

451

466

467

475

Based on this data, the next four procedure calls assign security:

EXEC pSecurity_Assign

@PersonCode = '118',

@AddressCode = '1',

@SecurityLevel = 3;

EXEC pSecurity_Assign

@PersonCode = '119',

@AddressCode = '1',

@SecurityLevel = 2;

c35.indd 865c35.indd 865 7/31/2012 9:59:40 AM7/31/2012 9:59:40 AM

http://www.it-ebooks.info