I want to grant permissions to a user to create,alter,execute on existing sp's or that will be created in future in dev env for SQL Server 2005
asked
Granting permissions to a user on existing and future stored procedures
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.
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...
GRANT EXECUTE TO [User] ON [Schema]
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:
Grant Execute to [user]
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.
