Comparing role securables

aculveraculver Posts: 16
edited January 12, 2009 11:12AM in SQL Compare Previous Versions
When I do a SQL Compare 6 the database roles and role memberships are compared and synchronized, but the role securables are not.

Am I missing a setting, or is SQL Compare 6 unable to do this?

Workarounds?

Many thanks -- Andrew

Comments

  • Michelle TMichelle T Posts: 566 Gold 1
    Role securables are synchronized with the securable object, not with the role. If you haven't got 'Ignore Permissions' selected, and you're synchronizing the relevant objects, the permissions that the roles have on the objects should be synchronized.

    Could you give an example of a securable that isn't being synchronized, so I can look into this?
    Software Developer
    Redgate Software
  • The securable object I am using is a schema. A database role has been assigned permissions on all objects belonging to a schema.

    For example, a new database role is created called partialdatawriter, and the dbo schema properties dialog has been used to grant select and update permission (for all objects in the dbo schema) for this role.

    These permissions are not compared or synchronized.
  • Michelle TMichelle T Posts: 566 Gold 1
    Apologies that it's taken a while to get back to you - I was a bit tied up with the recent releases.

    Does this problem happen when you're using any schema other than 'dbo'?

    SQL Compare currently ignores dbo as a system object, and unfortunately the way that permissions attach to objects means that it's also ignoring the role's permissions on dbo. It shouldn't be ignoring these permissions - I've put the problem in our bug tracking system (SC-3971) and hopefully we'll get it fixed shortly. Thank you very much for bringing this issue to our attention.
    Software Developer
    Redgate Software
  • Using SQL Compare 7 comparing a test database with permissions granted to roles by both the 'dbo' schema and one named 'TestSchema_1', the role permissions were compared and synchronized for 'TestSchema_1' but were ignored for schema 'dbo'.

    My understanding of SQL Server 2005 is that 'dbo' should be treated in this instance just like any other schema and that these permissions should synchronize.

    I look forward to early news on this matter.
  • Michelle TMichelle T Posts: 566 Gold 1
    Yes - we should be treating 'dbo' like any other schema in this regard and synchronizing the dependencies. We know that we aren't currently and we're working on getting it fixed. We'll reply to this thread with news when we've fixed it and released a version with the fix in. Is there some other way you'd like to be contacted when the fix is available?
    Software Developer
    Redgate Software
  • I'm getting emails for this thread so that's all the notice I need. Thanks for your prompt response.
  • This thread has gone dormant but I hope the problem is or has been addressed. I'm hoping Michelle Taylor or someone else from Red-Gate will give us an update.
  • Also, this topic is now a SQL Compare 7 issue and should probably be moved there.
  • The SQL Compare 8 beta (http://www.red-gate.com/MessageBoard/viewtopic.php?t=8303) should have fixed this issue. Could you download it and give it a go?
    Software Developer
    Redgate Software
  • Yes! It works in 8.0. Many thanks.
Sign In or Register to comment.