Giving selective permissions in a database

Question

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?

Answer 1

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]