Most people say that all sql code should be kept separate from your application in the database layer as stored procedures. Whenever I have done this in the past I have had to go into each stored procedure and set the execute permissions for the users. This is time consuming and error prone.
I realised that there must be an easier way of setting the execute permissions so that it only had to be set once for the database after a bit of searching I found the answer.
You just have to run the following sql code on the database.
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
This code creates a new role called 'db_executor' then grants executor permissions to the role.
After running this code you can add a user to the new role by running the following sql code.
EXEC sp_addrolemember 'db_executor', 'sqlusername'
The only draw back with this method of assigning execute permissions is that the user will get permissions to execute any stored procedure already created or created in the future.