Synchronize roles without synchronizing membership

thensley

We use SQL Compare to synchronize our development and production databases. However, the development databases will have different members for the database roles.

If SQL Compare sees a newly created role, I'd like it to synchronize the role and any object permissions for that role without also populating the role with members.

Is this possible?
:?

Brian Donahue

Hello,

Thanks for your post. SQL Compare always, always wants to populate the role with members, even with the 'include dependencies' option turned off. If the role member exists in the database already, the role member is added to the role. If it does not, it is added to the database.

rgribble

Hi Brian,

it may be the case that SQLCompare _ALWAYS_ wants to populate a role with users, however the entire point of using roles would be so that permissions on a database can be managed in different environments, DEV, INT, TEST, PROD etc having a controlled PERMIT/DENY permissions for each role on various database objects, but leaving the role membership to differ according to the environment

I would like to add my request that the feature the O.P is requesting be added to SQL Compare - an option that controls "script membership of roles"

This would make it a whole lot more useful for managing roles and permissions over various deployment environments, without affecting the membership of the roles. The roles would still be created, and permissions applied, but the sp_AddRoleMember would not be generated if the option wasnt enabled

In my case, we do not even want the same users that exist in our test environment to be added to the database in Production as we are ina regulated environment - access to sensitive production data is a serious security issue

rgribble

i was wondering if this feature request had been considered for the release coming later this year? I had heard possibilities that it was going to be in!?

Daniel Handley

Hi Ryan

This is being looked at for the next version of the product.

However this is dependant on time and resources.

Regards
Dan