Alter every stored procedure in a database that fits a given criteria

Recently I had to search for, and update, hard-coding server references for every stored procedure in a database. This amounted to altering over 60 stored procedures. Instead of going through each stored procedure, one-by-one, I used the following code to accomplish the task:

BEGIN TRANSACTION

CREATE TABLE #ProcsToChange (
	ProcName NVARCHAR(MAX) NULL
)

CREATE TABLE #tblHolding (
	ID INT IDENTITY(1,1) NOT NULL,
	proctext NVARCHAR(MAX) NULL
)

INSERT #ProcsToChange (ProcName)
SELECT DISTINCT OBJECT_NAME(sc.id)
FROM syscomments sc
INNER JOIN sysobjects so 
ON sc.id = so.id
WHERE sc.text LIKE '%[OldServer]%'
ORDER BY OBJECT_NAME(sc.id); 

DECLARE @newProcText NVARCHAR(MAX)
DECLARE @proc NVARCHAR(MAX)

DECLARE c CURSOR FOR
SELECT ProcName FROM #ProcsToChange;

OPEN c;
FETCH NEXT FROM c INTO @proc 

WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT #tblHolding (proctext)
	EXEC sp_helptext @proc
	
	PRINT @proc
	SELECT @newProcText = NULL;
	SELECT @newProcText = COALESCE(@newProcText,'') + proctext FROM #tblHolding ORDER BY ID ASC
	SELECT @newProcText = REPLACE(@newProcText, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
	SELECT @newProcText = REPLACE(@newProcText, '[OldServer]', '[NewServer]');

	--PRINT @newProcText;
	EXEC sp_executesql @newProcText;

	TRUNCATE TABLE #tblHolding
	
	FETCH NEXT FROM c INTO @proc
	
END
CLOSE c;
DEALLOCATE c;

DROP TABLE #ProcsToChange
DROP TABLE #tblHolding

--ROLLBACK
COMMIT

In order to grab only the stored procedures fitting your criteria, simply manipulate the WHERE clause that is used to filter data being inserted into #ProcsToChange.

This code should be thoroughly tested in a test environment before using in production. This code is provided as-is, with no warranty whatsoever.

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

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.