SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope.
What it means is that we can issue a statement like the example below and this will GRANT execute permissions on ALL existing stored procedures and scalar functions AND all subsequently created ones.
GRANT EXECUTE TO [myUser] AS [dbo]
If you only make use of stored procedures to access your database and have not implemented a complex security model, this is probably the easiest and cleanest way to manage access rights.
If users' Windows accounts are used to connect to the database, you will prefer creating a role like db_storedprocedures_executor and assigning it the EXECUTE permission. Then it is very much like the existing fixed database roles such as db_datareader.
