Excluding users based on regex not working?

charmon79

I'm writing a process which will use SQL Compare command line to sync all objects from a database into a new, empty database. I need to exclude Windows users in the database from the comparison, because they cannot be created in the new database (they come from a different domain & do not exist on this SQL Server's domain).

Reading the documentation made this look like a simple matter of setting an /exclude switch in the command line. So, I tried the following command line:

sqlcompare.exe /Server1:MyServer /Database1:MyDatabase /Server2:MyServer /Database2:MyDatabase_TEST /Synchronize /exclude:user:MyDomain* /LogLevel:Warning

However, SQL Compare is still comparing & trying to synchronize the MyDomain users I want it to ignore.

15:33:48.786|Fatal  |Command Line        |1  |Synchronization of 'MyServer.MyDatabase' and 'MyServer.MyDatabase_TEST' failed: Windows NT user or group 'MyDomain\JoeUser' not found. Check the name again.
Error executing the following SQL:
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\JoeUser')
CREATE LOGIN [MyDomain\JoeUser] FROM WINDOWS

Am I misusing the /exclude switch here?

Version: SQL Compare Command Line V12.3.3.4490

Mark J

Hi - sorry to hear you're having trouble with the commandline

I think there could be a couple of things going on here:

Firstly, the regex isn't quite correct, since we're interpreting the commandline option as a .NET regex rather than a glob, so MyDomain* will match things like MyDomai and MyDomain and MyDomainnnnnn. Adding .* at the end would be technically more correct.

However, that probably isn't the issue here, since we only need to match a substring of the user name for it to be excluded anyway. It looks like the line that's failing isn't actually creating a user in the database, but trying to create a server login for the user in case that is necessary. It's probably worth taking a look at the deployment script that the commandline is trying to run using the /scriptfile:<filename> argument - I'm guessing that the part of the deployment script that's failing will look something like this:

...
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'MyDomain\JoeUser')
CREATE LOGIN [MyDomain\JoeUser] FROM WINDOWS
GO
CREATE USER [Joe] FOR LOGIN [MyDomain\JoeUser]
...

In this case, it's the user Joe that we need to exclude rather than the login [MyDomain\JoeUser], so the commandline arguments would look something more like /exclude:user:Joe in this example