Archive for category Control

Limiting the number of executions of a stored procedure

Here’s a script that makes use of extended properties to limit the number of executions of a stored procedure. An example use-case is a stored procedure that allows your users to perform database backups. You may want to limit the number of times they can run the procedure.


IF OBJECT_ID('dbo.mytestproc') IS NOT NULL DROP PROCEDURE dbo.mytestproc
GO

CREATE PROCEDURE dbo.mytestproc
WITH EXECUTE AS OWNER
AS
BEGIN
DECLARE @procname sysname
DECLARE @numofallowedexecutions int

SET NOCOUNT ON

SET @procname = OBJECT_NAME(@@PROCID)

SELECT @numofallowedexecutions = CONVERT(int,value)
FROM sys.fn_listextendedproperty (NULL, 'schema', 'dbo', 'procedure', @procname, default, default)
WHERE name = 'numofallowedexecutions'

IF @numofallowedexecutions = 0
BEGIN
PRINT 'Sorry, you''ve run me often enough'
END
ELSE
BEGIN
PRINT 'I''m running, doing some work...'

SET @numofallowedexecutions = @numofallowedexecutions - 1

EXEC sys.sp_updateextendedproperty
@name = N'numofallowedexecutions'
,@value = @numofallowedexecutions
,@level0type = N'SCHEMA'
,@level0name = 'dbo'
,@level1type = N'PROCEDURE'
,@level1name = @procname
END
END
GO

-- Add the extended property
EXEC sys.sp_addextendedproperty
@name = N'numofallowedexecutions'
,@value = 5
,@level0type = N'SCHEMA'
,@level0name = N'dbo'
,@level1type = N'PROCEDURE'
,@level1name = N'mytestproc'
GO

-- Create a testing user that can invoke the procedure
IF USER_ID('testuser') IS NOT NULL DROP USER testuser
GO
CREATE USER testuser WITHOUT LOGIN
GO

GRANT EXECUTE ON dbo.mytestproc TO testuser
GO

-- Now test the number of executions
EXECUTE AS USER = 'testuser';
PRINT 'Invoking procedure as ' + USER_NAME();
GO

-- Now run it 5 times
EXEC dbo.mytestproc
GO 5

-- Now run it 1 more time and you should get the not allowed message
EXEC dbo.mytestproc
GO

REVERT;
PRINT 'Reverted as ' + USER_NAME();
GO

-- Tidy up
IF OBJECT_ID('dbo.mytestproc') IS NOT NULL DROP PROCEDURE dbo.mytestproc
GO
IF USER_ID('testuser') IS NOT NULL DROP USER testuser
GO

The output from this code is as follows:

Invoking procedure as testuser
Beginning execution loop
I’m running, doing some work…
I’m running, doing some work…
I’m running, doing some work…
I’m running, doing some work…
I’m running, doing some work…
Batch execution completed 5 times.
Sorry, you’ve run me often enough
Reverted as dbo

This is just one of many ways of using extended properties to control the execution of stored procedures.

Advertisements

Leave a comment