Comparing role securables

aculver

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

Michelle T

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?

aculver

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 T

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.

aculver

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 T

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?

aculver

I'm getting emails for this thread so that's all the notice I need. Thanks for your prompt response.

aculver

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.

aculver

Also, this topic is now a SQL Compare 7 issue and should probably be moved there.

Michelle T

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?

aculver

Yes! It works in 8.0. Many thanks.