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.

I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's [ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the [AD\dbusersgroup_limitedrights] I have created a schema with their corp initial

[DIS] [SUB] [TEC]

within these schemas they should have the rights to create their own tables, views and stored procedures, that are based on the [ICD] and [XCD] schemas objects.

I made the group now owner of the schema's [DIS],[SUB],[TEC] and on the other two schemas did grant them right for SELECT and EXECUTE. I also did make the group db_role db_datareader to make sure they don't get rights on other later created schema's.

However even though they are owner of their own schema they can't create objects within the schemas. I assume that this is due to the db role db_datareader.

So for such a scenario, what is best practice to give limited permissions within a database?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.

GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]
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.