User Rights - Can alter Sp's but not tables

Question

How can i make user permissions so that user can CREATE\ALTER\DELETE Store Procedures, but cannot CREATE\ALTER\DELETE tables in database.

Answer 1

I presume your users are creating reports or data extracts. You can grant permissions to a particular schema, so you could create a separate schema for your users and have the stored procedures reside in that schema. Configure the security on the rest of the objects so the users only have read permission.

You can also do something similar by creating a separate database and either require explicit reference to the tables or create synonyms within the reporting DB.

Answer 2

Use GRANT CREATE PROCEDURE TO ...

Better still, use schemas

Answer 3

Thanks for your response and that helps a lot. However i achieved it by creating a DDL trigger in my database so that i can capture all request for create, alter or delete for any table in my database. For that i have create a role and bound my login to that role(role is blank). And after that i checked if the create, alter or delete from that role than restrict user to do it from same.