SQL Server 2008 schema separation - Schema permissions and database roles

I really hope someone has some insight into this. Just to clarify what I'm talking about up front; when referring to Schema I mean the database object used for ownership separation, not the database create schema.

We use Sql Server Schema objects to group tables into wholes where each group belongs to an application. Each application also has it's own database login. I've just started introducing database roles in order to fully automate deployment to test and staging environment. We're using xSQL Object compare engine. A batch file is run each night to perform comparison and generate a script change file. This can then be applied to the target database along with code changes.

The issue I'm encountering is as folows. Consider the following database structure:

Database:

• Security/Schemas:
  • Core
    • CoreRole (owner)
    • SchemaARole (select, delete, update)
    • SchemaBRole (select)
  • SchemaA
    • SchemaARole (owner)
  • SchemaB
    • SchemaBRole (owner)
• S开发者_StackOverflowecurity/Roles/Database Roles:
  • CoreRole
    • core_login
  • SchemaARole
    • login_a
  • SchemaBRole
    • login_b

The set-up works perfectly well for the three applications that use these. The only problem is how to create / generate a script that creates schema -> role permissions? The owner role gets applied correctly. So for example, schema Core gets owner role CoreRole (as expected). However the SchemaARole and SchemaBRole do not get applied.

I wasn't able to find an option to turn this on within xSQL object nor does an option to script this from SQL Server management studio exist. Well, I can't find it at least.

Am I trying to do impossible? How does SQL Server manage this relationship then?

I just kicked up SQL Profiler & trapped what I think you scenario is. Try this:

GRANT SELECT ON [Core].[TestTable] TO [CoreRole]
GRANT DELETE ON [Core].[TestTable] TO [CoreRole]
GRANT UPDATE ON [Core].[TestTable] TO [CoreRole]