Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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.

share|improve this answer


Better still, use schemas

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.