Microsoft® SQL Server® 2012 Bible

866

Part VI: Securing Your SQL Server (Jose)

EXEC pSecurity_Assign

@PersonCode = '118',

@AddressCode = '2',

@SecurityLevel = 1;

EXEC pSecurity_Assign

@PersonCode = '120',

@AddressCode = '5',

@SecurityLevel = 2;

The following two commands test the data inserts using the pSecurity_Fetch procedure.

The fi rst test examines the security settings for the '5' location:

EXEC pSecurity_Fetch @AddressCode = '5';

Result:

PersonCode AddressCode SecurityLevel

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

120 5 2

The next batch examines the security setting for “Don Hall” (contact code 118):

EXEC pSecurity_Fetch @PersonCode = '118';

Result:

ContactCode LocationCode SecurityLevel

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

118 1 3

118 2 1

The row-based security schema includes several constraints. The following commands test

those constraints using the stored procedures:

Testing the unique constraint:

EXEC pSecurity_Assign

@PersonCode = '120',

@AddressCode = '5',

@SecurityLevel = 2;

Result:

Server: Msg 2627, Level 14, State 2,

Procedure pSecurity_Assign, Line 35

Violation of UNIQUE KEY constraint 'PersonAddress'.

Cannot insert duplicate key in object 'Security'.

The statement has been terminated.

Testing the valid security-code check constraint:

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

http://www.it-ebooks.info