Here is my current setup: Database Role - MyDbRole Schema - MySchema User - MyUser
MySchema is owned by MyDbRole. MyUser is mapped to my database that contains MyDbRole and MySchema. It is mapped using the MyDbRole database role.
I thought that MyUser would now have access to any object within the MySchema schema, because it is owned by MyDbRole, which I have assigned to the user. However, this isn't the case and only until I give db_owner or db_dataReader does it give access.
I have tried using the 'securables' bit of the dbrole properties and adding all objects in schema, but this requires me to go through each object and give individual permissions (which totally takes me back to the 2000 way).
Am I missing something? Is my setup somehow all wrong? Should this work or is it not possible to assign a user to a dbrole that owns a schema?