Competition: What’s your favorite Redgate tool? Enter now.

SQL Compare - Exclude User problem

Jamie ClaytonJamie Clayton Posts: 19
edited May 21, 2007 10:13PM in SQL Compare Previous Versions
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?
Jamie Clayton
Senior Software Developer
Jenasys Design Pty Ltd, Australia

Comments

  • Eddie DEddie D Posts: 1,688 Rose Gold 5
    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: [email protected]
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Thanks for the reply.
    I had been using the
    &lt;Exclude&gt;User:BNE-MYCUSTOMER\~ApplicationGroupName &#40;BNE&#41;&lt;/Exclude&gt;
    
    or
    &lt;Exclude&gt;User&lt;/Exclude&gt;
    
    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
    &lt;Exclude&gt;User*&lt;/Exclude&gt;
    
    entries and replace it with
    &lt;options&gt;IgnoreUsers&lt;/options&gt;
    

    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
    &lt;?xml version="1.0" encoding="utf-8"?&gt;
    &lt;commandline&gt;
      &lt;snapshot1&gt;C:\Data\Customers\xyz\SqlDatabaseMgmt\Managment\Snapshots\xyz_20070509.snp&lt;/snapshot1&gt;
      &lt;database2&gt;xyz_Test&lt;/database2&gt;
      &lt;server2&gt;sql.xyz.com.au&lt;/server2&gt;
      &lt;username2&gt;mySecureUserName&lt;/username2&gt;
      &lt;password2&gt;@password&lt;/password2&gt;
      &lt;scriptfile&gt;C:\Data\Customers\xyz\SqlDatabaseMgmt\Managment\Results\xyz_Test.sql&lt;/scriptfile&gt;
      &lt;exclude&gt;User:myApplication&lt;/exclude&gt;
      &lt;exclude&gt;User:BNE-MYCUSTOMER\~ApplicationGroupName &#40;BNE&#41;&lt;/exclude&gt;
      &lt;exclude&gt;Role:xyzUsersRole&lt;/exclude&gt;
      &lt;exclude&gt;Role:db_datareader&lt;/exclude&gt;
      &lt;options&gt;IgnoreUsers&lt;/options&gt;
      &lt;force/&gt;
      &lt;!-- &lt;verbose/&gt; --&gt;
    &lt;/commandline&gt;
    

    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.
    Jamie Clayton
    Senior Software Developer
    Jenasys Design Pty Ltd, Australia
Sign In or Register to comment.