Tuesday, 3 February 2015

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 YourDatabaseNameHereGOSELECT NAMEFROM 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