SQL Compare - Exclude User problem

Jamie Clayton

G'day,

I'm currently remotely supporting a clients databases and using the SQLCompare.exe command line + *.xml configuration arguments file to compare databases with snapshots.

One of the problems I'm facing is to do with Users (Microsoft Groups) in the clients database and how SQLCompare.exe excludes them

I have an entry in my arguments *.xml file like
<?xml version="1.0" encoding="utf-8"?>
<commandline>
..
<exclude>User:BNE-MYCUSTOMER\~ApplicationGroupName (BNE)</exclude>
..
</commandline>

But following the compare with a snapshot I get the following statement incorrectly.

PRINT N'Dropping users'
GO
DROP USER [BNE-MYCUSTOMER\~ApplicationGroupName (BNE)]
GO

If I run the full SQLCompare Application I can exclude the user successfully, so I'm assuming this is a syntax error with the characters in the <exclude></exclude> xml. I've tried a number of combination and they all seem to fail. Is it a PEBKAC or a bug with the command line?

Eddie D

The user you are trying to exclude, are there any objects dependent on the user? If the answer is yes, what options have you selected?

For example in your arguement file,

<commandline>

.....

<options>default</options> or <options>IncludeDependencies</options>or
<options>incd</options>

</commandline>

The default option turns the following options on, IgnoreWhiteSpace, IncludeDependencies, IgnoreFileGroups, IgnoreUserProperties, IgnoreWithElementOrder.

IncludeDependencies and it's alias incd, if selected as an option will also set the Include Dependencies to on.

Eddie Davis
Red Gate Software Ltd
Technical Support
E-Mail: support@red-gate.com

Jamie Clayton

Thanks for the reply.
I had been using the

<Exclude>User:BNE-MYCUSTOMER\~ApplicationGroupName (BNE)</Exclude>

or

<Exclude>User</Exclude>

elements without much luck.

I'm using Role based security within the database, so it tested the "Ignore users' permissions and role memberships" in the project properties for the SQL Compare GUI and got very close to the answer I was expecting.

If I then remove all my

<Exclude>User*</Exclude>

entries and replace it with

<options>IgnoreUsers</options>

I get completely the wrong results in the output script file. (I got this from the "options enumeration" help file). [Comparison to a snapshot].

To answer your question about Objects dependant on the user, I'm not sure exactly how to find the answer to that. However all the objects in the database are using DBO.ObjectName and there are no other user/group owned objects as this an application database managed by out company.

Here is how I'm doing it.
1. Have a *.bat file with the following

cd C:\Data\Customers\xyz\SqlDatabaseMgmt\Managment
DEL Results\*.sql
SQLCompare.exe /argfile:SQLCompare_xyzTest.xml
pause

2. Config .xml file with

<?xml version="1.0" encoding="utf-8"?>
<commandline>
  <snapshot1>C:\Data\Customers\xyz\SqlDatabaseMgmt\Managment\Snapshots\xyz_20070509.snp</snapshot1>
  <database2>xyz_Test</database2>
  <server2>sql.xyz.com.au</server2>
  <username2>mySecureUserName</username2>
  <password2>@password</password2>
  <scriptfile>C:\Data\Customers\xyz\SqlDatabaseMgmt\Managment\Results\xyz_Test.sql</scriptfile>
  <exclude>User:myApplication</exclude>
  <exclude>User:BNE-MYCUSTOMER\~ApplicationGroupName (BNE)</exclude>
  <exclude>Role:xyzUsersRole</exclude>
  <exclude>Role:db_datareader</exclude>
  <options>IgnoreUsers</options>
  <force/>
  <!-- <verbose/> -->
</commandline>

I'm expecting to have the Results\xyz_Test.sql file to be blank (non existant) because the only differents are the user and role membership of that user "BNE-MYCUSTOMER\~ApplicationGroupName (BNE)".

I guess I'm just having troubles working out the difference when driving the application from a command line (*.xml) file to the GUI, however I've got dozens of databases that are setup in similar ways, which I would like to compare nightly to ensure we don't have "db Administrators" breaking a database structure.