Grant EXECUTE permissions to all stored procedures and functions

Often times we need to grant EXECUTE rights to all stored procedures and functions in a database to a specific user.  Replace ‘YourUserName’ with the name of your login, and run on the database in question.  It will produce a line of SQL for each securable that requires a GRANT.

declare @username varchar(255)
set @username = 'YourLoginName'
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
and ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')

Advertisement