declare @sqlServerLogin varchar(100)
SET @sqlServerLogin = 'sqllogin'
SELECT N'GRANT EXEC ON ' +QUOTENAME(ROUTINE_SCHEMA) + N'.' +QUOTENAME(ROUTINE_NAME) +' TO ' +@sqlServerLogin +' '
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY( OBJECT_ID( QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
How to GRANT execute permission to all SPs in the database
For those , who are finding it difficult to GRANT permissions to the sps/fns, run the following, take the output from the resultpane and run it...
