Grant EXECUTE permissions to all stored procedures and functions

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')