Grant Execute Permission to User for SP & Functions

--Grants EXECUTE permission to TestUser for all user created procedures.

declare @username varchar(255)
set @username = 'TestUser'
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='PROCEDURE'

-- Grants EXECUTE permission to TestUser for all user created functions.

declare @username varchar(255)
set @username = 'TestUser'
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='FUNCTION'