List All Stored Procedures Within a SQL Database and Then Grant Execute on Them

It's been too long since I last blogged.

Thought I'd dust things off with this little snippet of T-SQL to list all stored procedures that exist within a specified SQL database:

USE YourDatabaseNameHere
GO

SELECT NAME
FROM SYSOBJECTS 
WHERE TYPE = 'P' 
AND LEFT(NAME,2) <> 'sp'
AND LEFT(NAME,2) <> 'dt'

This can be useful if you wish to grant execute permissions on these objects to your database login. For example, the following T-SQL will literally write out the commands you need to run in order to grant such execute permissions:

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO YourDatabaseLoginNameHere'
FROM SYSOBJECTS 
WHERE TYPE = 'P' 
AND LEFT(NAME,2) <> 'sp'
AND LEFT(NAME,2) <> 'dt'

You can take the output from the above, paste into a query window and run:

GRANT EXECUTE ON MyStoredProc TO YourDatabaseLoginNameHere

GRANT EXECUTE ON AnotherStoredProc TO YourDatabaseLoginNameHere

GRANT EXECUTE ON AndAnotherStoredProc TO YourDatabaseLoginNameHere