Microsoft® SQL Server® 2012 Bible

868

Part VI: Securing Your SQL Server (Jose)

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

Address: '99999' not found

Handling Security-Level Updates

The pSecurity_Assign procedure used in the previous examples handles new security

assignments but fails to accept adjustments to an existing security setting.

The following alteration to the procedure checks whether the security combination of

person and address is already in the Security table, and then performs the appropriate

insert or update. Security permissions may be created or adjusted with the new version

of the procedure and the same parameters. Here’s the improved procedure:

ALTER 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 BusinessEntityID = @PersonCode;

IF @PersonID IS NULL

BEGIN;

RAISERROR

('Person: ''%s'' not found', 15,1,@PersonCode);

RETURN -100;

END;

-- Get AddressID

SELECT @AddressID = AddressID

FROM Person.Address

WHERE AddressID = @AddressCode;

IF @AddressID IS NULL

BEGIN;

RAISERROR

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

RETURN -100;

END;

-- IS Update or Insert?

IF EXISTS(SELECT *

FROM dbo.Security

WHERE PersonID = @PersonID

AND AddressID = @AddressID)

-- Update

BEGIN;

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

http://www.it-ebooks.info