Synchronize roles without synchronizing membership

thensleythensley Posts: 3
edited September 21, 2005 7:20AM in SQL Compare Previous Versions
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?
:?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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
  • 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!?
  • Hi Ryan

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

    However this is dependant on time and resources.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
This discussion has been closed.