Ignore Roles

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Ignore Roles

Postby johnfnz » Wed Jul 03, 2013 10:54 am

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.
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby Chris Auckland » Fri Jul 05, 2013 12:02 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby johnfnz » Fri Jul 05, 2013 2:50 pm

/Options=IgnoreDependencies


doesn't seem to exist in my version. Am running 10.2
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby Chris Auckland » Fri Jul 05, 2013 2:57 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby johnfnz » Fri Jul 05, 2013 3:25 pm

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...
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby johnfnz » Fri Jul 05, 2013 3:40 pm

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:

Code: Select all
C:\\Program Files (x86)\\Red Gate\\SQL Compare 10> sqlcompare.exe /s2:localhost /u2:sa /p2:<..password..> /scr1:"<..path..>" /db2:<..Database..> /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: <..Valid Licence..>
Automation License: activated, edition: standard, serial number: <..Valid Licence..>
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 (from DB1 to DB2)

Summary Information
===================================================================================================================================
DB1 = <..database..>
DB2 = localhost.<..database..>

Object type             Name                                                                                               DB1 DB2
-----------------------------------------------------------------------------------------------------------------------------------
<..snip..>
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                                                                                  ==  ==
<..snip..>


johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby Chris Auckland » Fri Jul 05, 2013 3:43 pm

try it without the .* syntax. I'm not sure wildcards are supported in that way. e.g.

/include:View /exclude:Role
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby johnfnz » Fri Jul 05, 2013 3:46 pm

Sorry. Same behaviour.
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby Chris Auckland » Fri Jul 05, 2013 3:50 pm

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
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby johnfnz » Fri Jul 05, 2013 3:51 pm

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.
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby Chris Auckland » Mon Jul 08, 2013 9:42 am

yes, it's only a minor upgrade so your serial number will be valid.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Postby johnfnz » Mon Jul 08, 2013 4:30 pm

Have replicated this behaviour in 10.4.

Code: Select all
C:\\Program Files (x86)\\Red Gate\\SQL Compare 10>sqlcompare.exe /s2:localhost /u2:sa /p2:<..password..> /scr1:<..path..> /db2:<..database..> /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: <..valid serial..>
Automation License: activated, edition: standard, serial number: <..valid serial..>
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 (from DB1 to DB2)

Summary Information
===================================================================================================================================
DB1 = <..database..>
DB2 = localhost.<..database..>

Object type             Name                                                                                               DB1 DB2
-----------------------------------------------------------------------------------------------------------------------------------
<..snip..>
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                                                                                             ==  ==
<..snip..>
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby johnfnz » Wed Jul 10, 2013 9:38 am

Any update?
johnfnz
 
Posts: 11
Joined: Thu Jun 27, 2013 6:03 pm

Postby sebling » Thu Jul 11, 2013 4:49 pm

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 == ==
sebling
 
Posts: 15
Joined: Fri Jun 17, 2005 3:48 pm

Postby james.billings » Fri Jul 12, 2013 2:21 pm

Sorry this is still causing trouble- just trying to get it reproduced in-house.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Next

Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests