Microsoft® SQL Server® 2012 Bible

867

Chapter 35: Row-Level Security

35

EXEC pSecurity_Assign

@PersonCode = '118',

@AddressCode = '5',

@SecurityLevel = 5;

Result:

Server: Msg 547, Level 16, State 1,

Procedure pSecurity_Assign, Line 35

INSERT statement conflicted with COLUMN CHECK constraint

'ValidSecurityCode'. The conflict occurred in database

'AdventureWorks2012', table 'Security', column 'SecurityLevel'.

The statement has been terminated.

Testing the employees-only complex-business-rule trigger:

SELECT BusinessEntityID

FROM Person.Person

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

EXEC pSecurity_Assign

@PersonCode = '291',

@AddressCode = '3',

@SecurityLevel = 3;

Result:

Foreign Key Constraint: Security.PersonID

The next execution of the stored procedure tests the person foreign-key constraint and

generates an error because 99999 is an invalid contact.

EXEC pSecurity_Assign

@PersonCode = '99999',

@AddressCode = '1',

@SecurityLevel = 3;

Result:

Server: Msg 50000, Level 15, State 1, Procedure pSecurity_Assign, Line 19

Person: '99999' not found

Testing the location-code foreign-key constraint, it’s also checked within the stored

procedure:

EXEC pSecurity_Assign

@PersonCode = '118',

@AddressCode = '99999',

@SecurityLevel = 3;

Result:

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

http://www.it-ebooks.info