Command line synchronization error - Cannot find the user

instalectual

I am attempting to automatically synchronize a database on a test server as part of our build process which is executed for every commit to SVN. We use CruiseControl to execute the builds and I have been successfully generating change scripts using this process for other environments. This is the first time I have tried to deploy changes using the /synchronize switch on the command line.

I receive the following error when the command is executed by CruiseControl running under the Localy System Account:

Error: Synchronization of '.' and '[removed server info]' failed: Cannot
find the user '[removed user name]', because it does not exist or you do not
have permission.

The user it seems to be tripping over doesn't exist in the DB anymore and if I execute the command manually at a command prompt as an administrator it works without issue and changes are synchronized. I'm at a loss here. I would believe it to be a permissions issue if the user in the error actually existed. The cleansed command is below, the settings marked as [removed] are verified and correct:

sqlcompare /sourcecontrol1 /revision1:72023 /versionusername1:[removed] /versionpassword1:[removed] /Server2:[removed] /Database2:[removed] /synchronize /scriptsfolderxml:[removed] /filter:[removed] /include:StaticData /options:AddDatabaseUseStatement,IgnoreUsers,IgnorePermissions,IgnoreUsersPermissionsAndRoleMemberships

The source is the revision in SVN being built, the target is the database on the test server, the differences are to be synchronized by sql compare.

I appreciate any feedback on this error.

Brian Donahue

My best guess is the answer lies in the filter.

Presumably, like most people you are trying to avoid synchronizing Windows users, which cause problems when going from one environment to another.

If you set the options to ignore users, user permissions, and role memberships, it should be safe to filter out users. I guess the key would be to try to work out the particular SQL query SQL Compare ran which caused the error message to appear. the only practical way to do that AFAIK is to try running a SQL Profiler session on the server at the same time as the job runs.

At least then we would know what user and object(s) are involved in the query. Without that, it's impossible to say why you have a problem and how you can adjust the filter and/or command-line.

instalectual

I can run a trace while the synchronization happens. I don't think it's the filters however because I am able to run the command manually at the command line and it works. It's the exact same command using the same filters on which the automatic task is failing. Also, the user it fails to synchronize does not exist in the source or target, I think the issue would be related to whatever is causing the synchronization task to try and synchronize a object that is not in either database.

Brian Donahue

it's probably trying to add the user to a role or assign a permission. But like I said, with the options it should not be doing that, so it's a mystery.

The only thing I can think of is someone tried to create or edit the .scpf file by-hand and that causes unpredictable results.

Possibly the "user" complaint it's making is related to cruise control rather than SQL Compare... maybe it can't impersonate a user or something? In the command-line arguments, you do not use SQL auth, so it is trying to run as LocalSystem, which is more than likely not allowed to do operations on the database. There would have to be some impersonation taking place so the task runs as some other Windows user.

instalectual

It turns out that one of the table creation scripts in SVN was trying to grant permissions to the user and since the user no longer exists we encountered the error. I'm still not clear why that grant statement was in the table creation script but editing the script and committing seemed to resolve the issue. Hopefully editing the script directly doesn't have any unintended consequences.

-D

iil888

I had the same problem and got it solved editing the script and commiting.

Thanks for your help.

Regards

iil888