Grant execute permission for a user on all stored procedures in database?

Question

Create a role add this role to users, and then you can grant execute to all the routines in one shot to this role.

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDIT
This works in SQL Server 2005, I'm not sure about backward compatibility of this feature, I'm sure anything later than 2005 should be fine.

Without over-complicating the problem, to grant the EXECUTE on chosen database:

USE [DB]
GRANT EXEC TO [User_Name];

This is a solution that means that as you add new stored procedures to the schema, users can execute them without having to call grant execute on the new stored procedure:

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'asp_net')
DROP USER asp_net
GO

IF  EXISTS (SELECT * FROM sys.database_principals 
WHERE name = N'db_execproc' AND type = 'R')
DROP ROLE [db_execproc]
GO

--Create a database role....
CREATE ROLE [db_execproc] AUTHORIZATION [dbo]
GO

--...with EXECUTE permission at the schema level...
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

--http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html
--Any stored procedures that are created in the dbo schema can be 
--executed by users who are members of the db_execproc database role

--...add a user e.g. for the NETWORK SERVICE login that asp.net uses
CREATE USER asp_net 
FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] 
WITH DEFAULT_SCHEMA=[dbo]
GO

--...and add them to the roles you need
EXEC sp_addrolemember N'db_execproc', 'asp_net';
EXEC sp_addrolemember N'db_datareader', 'asp_net';
EXEC sp_addrolemember N'db_datawriter', 'asp_net';
GO

Reference: Grant Execute Permission on All Stored Procedures

use below code , change proper database name and user name and then take that output and execute in SSMS. FOR SQL 2005 ABOVE

USE <database_name> 
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '  
from sys.objects  
where type ='P' 
and is_ms_shipped = 0