Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

3 Answers

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
add comment

Use GRANT CREATE PROCEDURE TO ...

Better still, use schemas

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.