Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Overview

When scripting stored procedure code for deployment to several servers, there are a few things to keep in mind.  One of those things is the permissions associated with the procedure you’re deploying.  When deploying a brand new stored procedure, the process of assigning permissions is fairly straightforward.  If you need to deploy an update to an existing stored procedure, things get a bit more complicated.  If you simply issue a DROP PROCEDURE call followed by the CREATE PROCEDURE code, you will lose the permissions that are associated with the stored procedure.  Issuing an ALTER PROCEDURE command will preserve the permissions but will cause you trouble if there are places where the procedure doesn’t yet exist.  To solve this issue, you should script out the permissions on a procedure to a temp table before dropping the procedure.  If there is a chance that the batch will fail, use a persisted table or a global temp table instead. Once the proc is recreated, run through the permissions and apply them to the proc again.  This way, you’ve preserved the permissions on the proc.

Now, there are some pitfalls to this approach.  If the proc didn’t already exist when you run the deploy script, there will obviously be no permissions to script out to a temp table.  So, you might run into the problem where the same proc on two different databases has two sets of permissions.  This might be an issue, but considering the proc didn’t exist in the first place, it’s doubtful that it would be an issue.  Nevertheless it is something to keep in mind.

The way to get around this pitfall is to include desired the permissions in the SQL script used to create the procedure. This way, the newly created proc will have the permissions it had before, in addition to any included in the deployment script.

The Code

Here is some sample SQL for scripting the permissions into a temp table:

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SampleProc')
BEGIN
	IF OBJECT_ID('tempdb..#tmp_PermissionsWork') IS NOT NULL DROP TABLE #tmp_PermissionsWork
	CREATE TABLE #tmp_PermissionsWork (
		  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL 
		, Permission NVARCHAR(MAX) NULL
	)
	
	INSERT #tmp_PermissionsWork (Permission)
	SELECT state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + QUOTENAME(USER_NAME(grantee_principal_id)) + ';' FROM sys.database_permissions 
	WHERE major_id = OBJECT_ID('SampleProc')

	DROP PROCEDURE SampleProc
END
GO

CREATE PROCEDURE SampleProc
AS
...
GO

DECLARE @cmd NVARCHAR(MAX)

WHILE EXISTS(SELECT 1 FROM #tmp_PermissionsWork)
BEGIN
	SELECT TOP 1 @cmd = Permission
	FROM #tmp_PermissionsWork
	
	EXEC sp_executesql @cmd;
	
	DELETE FROM #tmp_PermissionsWork 
	WHERE Permission = @cmd
END
GO

To use this code, swap out ‘SampleProc’ with your stored procedure and put your procedure code in place of lines 17 – 20.

*This code is provided “AS-IS” with no warranty of any kind.

Leave a Reply

Your email address will not be published. Required fields are marked *