See: http://www.sqldbatips.com/showarticle.asp?ID=8

In SQL Server 2000 we had to grand execute rights to all stored procedures by altering the create scripts or executing a sql query like:
declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME(‘<insert_username>’)
set @sql =’select ”grant exec on ” + QUOTENAME(ROUTINE_SCHEMA) + ”.” +
QUOTENAME(ROUTINE_NAME) + ” TO ‘ + @u + ”’ FROM INFORMATION_SCHEMA.ROUTINES ‘ +
‘WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),”IsMSShipped”) = 0’
exec master.dbo.xp_execresultset @sql,@db
In SQL Server 2005 we can grant execute rights to a role instead of a stored procedure:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor