Grant Execute Permission on All Stored Procedures

Right out of the box, SQL Server makes it pretty easy to grant SELECT, INSERT, UPDATE, and DELETE to all user tables.  That's accomplished by using the built-in db_datareader (SELECT) and db_datawriter (INSERT, UPDATE, and DELETE) database roles in every user database.  Any user you add to those database roles will be granted those permissions.

But what if you want to grant EXECUTE permission to all of the user stored procedures.  Where's the built-in database role for that?  Nowhere to be found. 

Oh you can create a database role, add users to that role, and grant EXECUTE permission to that role for each stored procedure that's created.  But that's just not as efficient as the built-in roles, mainly because you must always remember to grant the permission to the role every time you create a new stored procedure.

Here's an easier way to create a database role that's more like the built-in roles.

Create a database role and add a user to that role.

CREATE ROLE db_execproc;
GO
EXEC sp_addrolemember N'db_execproc', N'User1';
GO

Grant EXECUTE permission at the schema level.

GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

Create a new stored procedure.

CREATE PROCEDURE dbo.p_test
AS
SELECT * FROM dbo.Table1;
GO

Login as the test user and execute the stored procedure.

EXEC dbo.p_test;
GO

This example granted EXECUTE permission to the dbo schema.  Any stored procedures that are created in the dbo schema can be executed by users who are members of the db_execproc database role. You can grant the same permission to other schemas if needed.  

This concept is not going to be for everyone and every database, but if you're already using the built-in database roles to manage permissions within a database then this will be a good option for you.

For more details on database roles and grant schema permissions, check out Books Online.

Built-in Database Roles
http://msdn.microsoft.com/en-us/library/ms189121(v=sql.110).aspx

GRANT Schema Permissions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187940(v=sql.110).aspx