Ignore Roles

johnfnzjohnfnz Posts: 13
edited July 12, 2013 9:45AM in SQL Compare Previous Versions
Have had a look through the forums but can't find an answer to this one.

In my sync I want to completely ignore roles.

I am working from the command line. I have options:
IgnoreUsersPermissionsAndRoleMemberships,IgnoreUsers

set and am also specifying:
/exclude=Role:.*"
/exclude=User:.*"

and yet in the output from sqlcompare I still see:
Role db_access******* == ==
Role db_security******* == ==
Role db_backupoperator == ==
Role db_denydatareader == ==
Role db_denydatawriter == ==
Role public == ==

Can anyone suggest what I am doing wrong here?

We are dealing with the DB roles as part of a separate process so don't really care about differences (or equalities) in them during this sync.

Comments

  • Thanks for your post.

    I would imagine the problem is that another object in your project had a dependency on the role and it's being brought back in.

    By default, we bring back dependent objects, as they're probably required or it's likely the script will fail or it will leave your database in an inconsistent state.

    If you want to ignore dependencies, you can use the option 'IgnoreDependencies'

    I hope this helps.
    Chris
  • /Options=IgnoreDependencies

    doesn't seem to exist in my version. Am running 10.2
  • Sorry my fault.

    It's IncludeDependencies and that's one of the default options, so your original command should have worked :S

    Can you post your original command, the options switch should have a : rather than an = sign. e.g. /options:option1,option2
    Chris
  • I spotted this. I did have Default as one of the options specified. So I removed it but still the same behaviour.

    The command is being built programmatically - I'll dig it out and post...
  • OK. Got a command from the command line that replicates this behaviour. I have slightly anonymised the output as this is a public forum but please be assured the bits <..like this..> contain valid values. See output below:
    C:\Program Files &#40;x86&#41;\Red Gate\SQL Compare 10&gt; sqlcompare.exe /s2:localhost /u2:sa /p2:&lt;..password..&gt; /scr1:"&lt;..path..&gt;" /db2:&lt;..Database..&gt; /synchronise /include:Schema:.* /include:Rule:.* /include:Function:.* /include:UserDefinedType:.* /include:StoredProcedure:.* /include:Synonym:.* /include:Table:.* /include:View:.* /include:Identical /exclude:Role:.* /exclude:User:.* /Options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnoreUsersPermissionsAndRoleMemberships,IgnoreConstraintNames,IgnorePermissions,IgnoreExtendedProperties,IgnoreUsers
    
    SQL Compare: activated, edition: professional, serial number: &lt;..Valid Licence..&gt;
    Automation License: activated, edition: standard, serial number: &lt;..Valid Licence..&gt;
    SQL Compare Command Line V10.2.0.1337
    ==================================================================================================================================
    Copyright © Red Gate Software Ltd 1999-2012
    
    Registering data sources
    Creating mappings
    Comparing
    Applying Command Line Items
    Retrieving migration scripts
    Creating SQL
    Deploying changes &#40;from DB1 to DB2&#41;
    
    Summary Information
    ===================================================================================================================================
    DB1 = &lt;..database..&gt;
    DB2 = localhost.&lt;..database..&gt;
    
    Object type             Name                                                                                               DB1 DB2
    -----------------------------------------------------------------------------------------------------------------------------------
    &lt;..snip..&gt;
    Role                    db_owner                                                                                           ==  ==
    Role                    db_accessadmin                                                                                     ==  ==
    Role                    db_datareader                                                                                      ==  ==
    Role                    db_datawriter                                                                                      ==  ==
    Role                    db_ddladmin                                                                                        ==  ==
    Role                    db_securityadmin                                                                                   ==  ==
    Role                    db_backupoperator                                                                                  ==  ==
    Role                    db_denydatareader                                                                                  ==  ==
    Role                    db_denydatawriter                                                                                  ==  ==
    &lt;..snip..&gt;
    
    
    
  • try it without the .* syntax. I'm not sure wildcards are supported in that way. e.g.

    /include:View /exclude:Role
    Chris
  • Sorry. Same behaviour.
  • Can you try upgrading to SQL Compare 10.4? If you get the same behavior on the latest, I'll create a similar project and see if I can recreate the problem.
    Chris
  • Will our 10.2 licences be equally valid with that?

    This'll take time as I'll need to get approval to get new software installed.
  • yes, it's only a minor upgrade so your serial number will be valid.
    Chris
  • Have replicated this behaviour in 10.4.
    C:\Program Files &#40;x86&#41;\Red Gate\SQL Compare 10&gt;sqlcompare.exe /s2:localhost /u2:sa /p2:&lt;..password..&gt; /scr1:&lt;..path..&gt; /db2:&lt;..database..&gt; /synchronise /include:Schema /include:Rule /include:Function /include:UserDefinedType /include:StoredProcedure /include:Synonym /include:Table /include:View /include:Identical /exclude:Role /exclude:User /Options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,IgnoreUsersPermissionsAndRoleMemberships,IgnoreConstraintNames,IgnorePermissions,IgnoreExtendedProperties,IgnoreUsers
    
    SQL Compare: activated, edition: professional, serial number: &lt;..valid serial..&gt;
    Automation License: activated, edition: standard, serial number: &lt;..valid serial..&gt;
    SQL Compare Command Line V10.4.8.87
    ==================================================================================================================================
    Copyright © Red Gate Software Ltd 1999-2013
    
    Registering data sources
    Creating mappings
    Comparing
    Applying Command Line Items
    Retrieving migration scripts
    Checking for identical databases
    Creating SQL
    Deploying changes &#40;from DB1 to DB2&#41;
    
    Summary Information
    ===================================================================================================================================
    DB1 = &lt;..database..&gt;
    DB2 = localhost.&lt;..database..&gt;
    
    Object type             Name                                                                                               DB1 DB2
    -----------------------------------------------------------------------------------------------------------------------------------
    &lt;..snip..&gt;
    Role                    db_accessadmin                                                                                     ==  ==
    Role                    db_ddladmin                                                                                        ==  ==
    Role                    db_datawriter                                                                                      ==  ==
    Role                    db_backupoperator                                                                                  ==  ==
    Role                    db_denydatareader                                                                                  ==  ==
    Role                    db_datareader                                                                                      ==  ==
    Role                    db_securityadmin                                                                                   ==  ==
    Role                    db_owner                                                                                           ==  ==
    Role                    db_denydatawriter                                                                                  ==  ==
    Role                    public                                                                                             ==  ==
    &lt;..snip..&gt;
    
  • Any update?
  • I am seeing similar behavior with 10.2 using XML

    <options>IgnorePermissions,IgnoreFillFactor,IgnoreUserProperties,ForceColumnOrder,IgnoreStatistics,IgnoreUsers,IgnoreReplicationTriggers,IgnoreExtendedProperties,IgnoreFileGroups,IgnoreWhiteSpace</options>
    ...
    <exclude>Schema</exclude>
    <exclude>Role</exclude>
    <exclude>User</exclude>
    ...
    <reportAllObjectsWithDifferences />

    report shows
    Role db_owner == ==
    Role db_accessadmin == ==
    Role db_datareader == ==
    Role db_datawriter == ==
    Role db_ddladmin == ==
    Role db_securityadmin == ==
    Role db_backupoperator == ==
    Role db_denydatareader == ==
    Role db_denydatawriter == ==
    Role public == ==
    Schema db_owner == ==
    Schema db_accessadmin == ==
    Schema db_datawriter == ==
    Schema db_ddladmin == ==
    Schema db_securityadmin == ==
    Schema db_backupoperator == ==
    Schema db_denydatareader == ==
    Schema db_denydatawriter == ==
    Schema INFORMATION_SCHEMA == ==
    Schema dbo == ==
    Schema guest == ==
    Schema sys == ==
  • James BJames B Posts: 1,124 Silver 4
    Sorry this is still causing trouble- just trying to get it reproduced in-house.
    Systems Software Engineer

    Redgate Software

  • James BJames B Posts: 1,124 Silver 4
    OK, I think the main issue here is that the include:identical is "winning" (all the Roles you see are identical ones, not ones where there's a difference).
    I don't think there's any way to combine include/exclude like this and actually control what happens. It's recommended to use a filter rather than exclude now anyway (so create a filter excluding roles in the GUI and save it to a .scpf file, then reference this with the /filter switch).

    Even doing this, include:identical "wins", however there's a new switch now which omits the need for that: /assertidentical.
    Using that switch will stop the error occurring on identical DB's but also not include identical objects in the output.
    Systems Software Engineer

    Redgate Software

  • That doesn't help because:
    Error: The /assertidentical switch cannot be used in conjunction with the /synchronize switch. Please use SQLCompare.exe /? or
    SQLCompare.exe /verbose /? for more information.
    

    If I remove include:idential then I just get the
    Error: The selected objects are identical or no objects have been selected in the comparison.
    

    error.

    I'll try the filer.
Sign In or Register to comment.