I agree with Matt and in addition I would also suggest that you consider taking it a step further by creating a Database Role to manage this security implementation. Assigning a DB Role with EXECUTE to the Schema would mean that new users added to the database that also required the same privileges could simply be added to the role.
Doing so provides a clear and scalable security architecture.
answered Mar 23 '10 at 04:55 AM
You'll need to grant execute to the user on the schema, this way they'll have the permission at the schema level. If you have multiple schemas, you'll need multiple grants...
answered Mar 23 '10 at 04:25 AM
Matt Whitfield ♦♦
If you want to control the sps by schema, then I think Matt has the answer. If you want them to be able to execute all stored procedures for that database, simply:
This of course gives them considerable authority and I would generally not use it in production, but it may be what you want in a development environment.
In fact, in a development environment I routinely add developers to the database owner role, it makes things simpler and while it assumes some risk in a development environment it can be a good trade off to make.
answered Mar 23 '10 at 12:58 PM