SQL to Grant EXECUTE & SELECT permission to all Procedures and Functions

I was Googling around the other day for a bit of TSQL to quickly grant a user permission to use all procedures and functions and came across the following post. We have a few table -valued functions in our system so this script buckled with the following error;

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.

The SELECT permission must be granted on this object rather than EXECUTE as for scalar functions. Here’s an improvement on the script I found that will cater to this need.

SELECT 'GRANT ' + CASE(ROUTINE_TYPE)
                       WHEN 'PROCEDURE' THEN 'EXECUTE '
                       WHEN 'FUNCTION' THEN CASE(DATA_TYPE)
                                             WHEN 'TABLE' THEN 'SELECT '
                                             ELSE 'EXECUTE '
                                            END
                  END
                                + 'ON [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + '] TO [user];'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0;

Here’s some sample TSQL created when executed against the AdventureWorks database.

GRANT EXECUTE ON [dbo].[uspPrintError] TO [user];
GRANT EXECUTE ON [dbo].[uspLogError] TO [user];
GRANT EXECUTE ON [dbo].[ufnLeadingZeros] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetAccountingStartDate] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetAccountingEndDate] TO [user];
GRANT SELECT ON [dbo].[ufnGetContactInformation] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetProductDealerPrice] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetProductListPrice] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetProductStandardCost] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetStock] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetDocumentStatusText] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetPurchaseOrderStatusText] TO [user];
GRANT EXECUTE ON [dbo].[ufnGetSalesOrderStatusText] TO [user];
GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [user];
GRANT EXECUTE ON [dbo].[uspGetEmployeeManagers] TO [user];
GRANT EXECUTE ON [dbo].[uspGetManagerEmployees] TO [user];
GRANT EXECUTE ON [dbo].[uspGetWhereUsedProductID] TO [user];