List ALL Stored Procedures in an SQL Server Database

Today I ran into a unique problem. I needed to reassign the permissions on a bunch of Stored Procedures in an SQL Server database. What a joyless task.

Now, I could do this by hand. Just open up Microsoft SQL Server Management Studio, right click on the Stored Procedure, and assign the permissions. In my case, however, I had hundreds of Stored Procedures to change. Doing this one by one would make me want to claw my eyes out.

The solution is to use some hidden system tables to get the list for me.

SELECT [name]
FROM sys.sysobjects
WHERE (type = 'P')

This gives me the list I want, but I can do a bit better:

SELECT 'GRANT EXECUTE ON OBJECT::[dbo].[' + name + '] TO [MyUserID];' AS Expr1
FROM sys.sysobjects
WHERE (type = 'P')
AND (LEFT([name],3) = 'DNN')

With this SQL, I generate the actual command needed to grand the new permissions to the Stored Procedure. I also use a WHERE condition to limit the effect to just Stored Procedures beginning with DNN, so I don't do this to everything, just the ones I want.

Once I run this, I just copy the results, paste it back into the SQL section of Microsoft SQL Server Management Studio, and execute. Bingo! My permissions have been altered.

Enjoy.