Grant execute permission to all stored procedures

FYI

GRANT EXECUTE TO UserOrRoleName;

This will grant execute permission for all the objects to which this permission could be applied. Not just for the stored procedures but the scalar and aggregate functions, synonyms and CLR types too. This should be kept in mind.

To grant permissions for a user or role to execute any procedure in a database:

USE MyDatabase
GRANT EXECUTE TO UserOrRoleName;

To grant permissions for a user or role to execute any procedure in any database:

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName'

Depending on your schema structure, you could grant execute on a schema's procs i.e.

use MyDatabase
go
GRANT EXECUTE ON SCHEMA::[dbo] TO MyUser
go

If there's no simplier way you can iterate through all the stored procedures in the db and grant the permissions on them. Here's an example:

-- Grant execute on all stored procedures in the current database
DECLARE @name nvarchar(128)
DECLARE procCursor CURSOR FOR SELECT Name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1

OPEN procCursor
FETCH NEXT FROM procCursor INTO @name

WHILE  @@FETCH_STATUS = 0
BEGIN  
    EXEC ('grant execute on [' + @name + ']  to TheUserName')

    FETCH NEXT FROM procCursor INTO @name
END

CLOSE procCursor
DEALLOCATE procCursor
GO

This can be used inside a stored procedure whith a parameter for the username for flexibility.

If you are using SQL Server 2005 or 2008 and you want always grant EXECUTE permission to a stored procedure to some users(roles) and do not want to explicitly grat the rights, you can create a DDL trigger for such purpose. As example belows automatically grants EXECUTE permission to [public] on every newly created store.

You can easily modify the trigger to grant this permission to particular users or only grant permissions to stored proc in particular schema etc.

CREATE TRIGGER trg_GrantExecuteToPublicOnStoredProcs ON DATABASE
AFTER CREATE_PROCEDURE
AS
BEGIN
    DECLARE @eventData as xml
    DECLARE @schemaName nvarchar(128)
    DECLARE @objectName nvarchar(128)
    DECLARE @sql nvarchar(4000)

    SET @eventData = EVENTDATA()
    SELECT 
    	@schemaName = @eventData.value('/EVENT_INSTANCE[1]/SchemaName[1]', 'nvarchar(128)'),
    	@objectName = @eventData.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'nvarchar(128)')

    SELECT @sql = 'GRANT EXECUTE ON [' + @schemaName + '].[' + @objectName + '] TO [public]'
    EXEC sp_executesql @sql
END