Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds two MCITP certifications in SQL Server 2008. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.
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;
EXEC sp_addrolemember N'db_execproc', N'User1';
Grant EXECUTE permission at the schema level.
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
Create a new stored procedure.
CREATE PROCEDURE dbo.p_test
SELECT * FROM dbo.Table1;
Login as the test user and execute the stored procedure.
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.