You'll like this Jeff 🙂

Code windows mangle it a bit so it is attached too - recommend anyone interested opens that instead.

Paul

-- Creates and drops objects in tempdb only

-- Requires dbo permissions

-- You can run it all as one script, but it is better run one batch at a time

USE tempdb;

GO

--

-- This table only ever holds one row, containing the next available range start value

--

CREATE TABLE dbo.AllocationMaster

(

row_idINTEGER IDENTITY PRIMARY KEY

CHECK (row_id = 1),

next_available_valueINTEGER NOT NULL

CHECK (next_available_value BETWEEN 800000000 AND 900000000)

DEFAULT (800000000),

);

GO

--

-- The table of allocated ranges

--

CREATE TABLE dbo.Allocation

(

range_idINTEGER IDENTITY PRIMARY KEY,

range_nameNVARCHAR(30) NOT NULL UNIQUE,

range_startINTEGER NOT NULL

CHECK (range_start BETWEEN 800000000 AND 900000000),

range_endINTEGER NOT NULL

CHECK (range_end BETWEEN 800000000 AND 900000000),

CHECK (range_end >= range_start)

);

GO

--

-- Create a user and schema so we can break ownership chaining on the procedure

--

CREATE USER Demo WITHOUT LOGIN;

GO

CREATE SCHEMA Demo AUTHORIZATION Demo;

GO

--

-- The procedure which does the work

-- Created in the Demo schema so that permissions don't 'just work'

--

CREATE PROCEDURE Demo.usp_Allocate

@range_nameNVARCHAR(30),

@range_sizeINTEGER

WITH EXECUTE AS CALLER

AS

BEGIN

SETXACT_ABORT ON;

SETNOCOUNT ON;

DECLARE@Allocation

TABLE(

range_nameNVARCHAR(30) NOT NULL,

range_startINTEGER NOT NULL,

range_endINTEGER NOT NULL

);

BEGIN TRY

BEGIN TRANSACTION;

-- Atomic update and range allocate

-- We use the table variable rather than OUTPUT directly to dbo.Allocation

-- since that would prevent CHECK constraints, FOREIGN KEYs, or active TRIGGERs

UPDATEdbo.AllocationMaster

SETnext_available_value = next_available_value + @range_size

OUTPUT@range_name,

DELETED.next_available_value,

INSERTED.next_available_value - 1

INTO@Allocation (range_name, range_start, range_end)

WHERErow_id = 1;

-- Insert the range record from the table variable

INSERTdbo.Allocation

(range_name, range_start, range_end)

SELECTrange_name, range_start, range_end

FROM@Allocation;

-- Clean exit

COMMIT TRANSACTION;

RETURN(1 - @@ROWCOUNT);

END TRY

BEGIN CATCH

IFXACT_STATE() 0 ROLLBACK TRANSACTION;

-- Custom error handling here

SELECTErrorMsg = ERROR_MESSAGE();

RETURN999;

END CATCH;

END;

GO

--

-- Initialize the master table (trying to add another row after this produces an error)

--

INSERTdbo.AllocationMaster DEFAULT VALUES;

GO

--

-- Show table contents

--

SELECT * FROM dbo.AllocationMaster;

GO

--

-- Revoke permissions (not that we granted any, but hey, it's a demo)

--

REVOKE SELECT, INSERT, DELETE, UPDATE ON dbo.AllocationMaster FROM public;

REVOKE SELECT, INSERT, DELETE, UPDATE ON dbo.Allocation FROM public;

GO

--

-- Create a test login and user to test the procedure with

--

CREATE LOGIN testuser WITH PASSWORD = N'Test=User=12345';

CREATE USER testuser;

GO

--

-- Grant permission on the procedure to this test user

--

GRANT EXECUTE ON Demo.usp_Allocate TO testuser;

GO

--

-- Check the permissions

--

EXECUTE AS USER = N'testuser';

DECLARE@ReturnCode INTEGER;

--

-- Fails on permissions check (SELECT and UPDATE on dbo.AllocationMaster)

--

EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 1000;

SELECTTestRun = @ReturnCode;

REVERT;

GO

--

-- Create a certificate to sign the procedure with

--

CREATE CERTIFICATE RangeAllocationCert

ENCRYPTION BY PASSWORD = '3%67&yU!"-9w4}Z+'

WITH SUBJECT = 'Range Allocation Permissions',

START_DATE = '2000-01-01', EXPIRY_DATE = '9999-12-31';

GO

--

-- Create a user from the certificate (we grant permissions to this 'user')

-- This user cannot log in

--

CREATE USER RangeAllocationUser FOR CERTIFICATE RangeAllocationCert;

GO

--

-- Grant specific object permissions to the certificate 'user'

--

GRANT SELECT ON dbo.AllocationMaster TO RangeAllocationUser;

GRANT UPDATE ON dbo.AllocationMaster TO RangeAllocationUser;

GRANT INSERT ON dbo.Allocation TO RangeAllocationUser;

GO

--

-- Sign the procedure with the certificate

-- Callers of the procedure get the additional permissions

-- *while running the procedure only*

--

ADD SIGNATURE TO Demo.usp_Allocate BY CERTIFICATE RangeAllocationCert WITH PASSWORD = '3%67&yU!"-9w4}Z+';

GO

--

-- Test the allocation procedure with the new permissions arrangements

--

EXECUTE AS USER = N'testuser';

GO

DECLARE@ReturnCode INTEGER;

EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 1000-- Succeeds now

SELECTAddRangeA = @ReturnCode;

GO

DECLARE@ReturnCode INTEGER;

EXECUTE @ReturnCode = Demo.usp_Allocate N'Range B', 4000-- Succeeds now

SELECTAddRangeB = @ReturnCode;

GO

DECLARE@ReturnCode INTEGER;

EXECUTE @ReturnCode = Demo.usp_Allocate N'Range C', 9500-- Succeeds now

SELECTAddRangeC = @ReturnCode;

GO

DECLARE@ReturnCode INTEGER;

EXECUTE @ReturnCode = Demo.usp_Allocate N'Range A', 100-- Fails due to duplicate range name

SELECTAddRangeA = @ReturnCode;

GO

--

-- Finished being the test user

--

REVERT;

GO

--

-- Show the contents of the tables

--

SELECT row_id, next_available_value FROM dbo.AllocationMaster;

SELECT range_id, range_name, range_start, range_end FROM dbo.Allocation;

GO

--

-- Tidy up

-- (if you get permissions errors here, make sure you executed REVERT above)

--

DROP TABLE dbo.AllocationMaster;

DROP TABLE dbo.Allocation;

DROP PROCEDURE Demo.usp_Allocate;

DROP USER RangeAllocationUser;

DROP CERTIFICATE RangeAllocationCert;

DROP USER testuser;

DROP LOGIN testuser;

DROP SCHEMA Demo;

DROP USER Demo;

GO