Red Gate forums :: View topic - Ignore Roles
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Ignore Roles

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Wed Jul 03, 2013 10:54 am    Post subject: Ignore Roles Reply with quote

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:

Quote:
IgnoreUsersPermissionsAndRoleMemberships,IgnoreUsers


set and am also specifying:

Quote:
/exclude=Role:.*"
/exclude=User:.*"


and yet in the output from sqlcompare I still see:

Quote:
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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Jul 05, 2013 12:02 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Fri Jul 05, 2013 2:50 pm    Post subject: Reply with quote

Quote:
/Options=IgnoreDependencies


doesn't seem to exist in my version. Am running 10.2
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Jul 05, 2013 2:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Fri Jul 05, 2013 3:25 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Fri Jul 05, 2013 3:40 pm    Post subject: Reply with quote

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:

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..>


Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Jul 05, 2013 3:43 pm    Post subject: Reply with quote

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

/include:View /exclude:Role
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Fri Jul 05, 2013 3:46 pm    Post subject: Reply with quote

Sorry. Same behaviour.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Fri Jul 05, 2013 3:50 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Fri Jul 05, 2013 3:51 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Jul 08, 2013 9:42 am    Post subject: Reply with quote

yes, it's only a minor upgrade so your serial number will be valid.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Mon Jul 08, 2013 4:30 pm    Post subject: Reply with quote

Have replicated this behaviour in 10.4.

Code:

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..>
Back to top
View user's profile Send private message
johnfnz



Joined: 27 Jun 2013
Posts: 11

PostPosted: Wed Jul 10, 2013 9:38 am    Post subject: Reply with quote

Any update?
Back to top
View user's profile Send private message
sebling



Joined: 17 Jun 2005
Posts: 15

PostPosted: Thu Jul 11, 2013 4:49 pm    Post subject: Reply with quote

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 == ==
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1116
Location: My desk.

PostPosted: Fri Jul 12, 2013 2:21 pm    Post subject: Reply with quote

Sorry this is still causing trouble- just trying to get it reproduced in-house.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group