In Sql Server 2005, how do I setup permissions using schemas and db roles?

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?

---

These link may help.

SQL Server 2005 Managing Permissions

Ownership and User-Schema Separation in SQL Server