Exclude Logins from SQL database compare

dUros

Hello

I use Powershell cmdlets to compare two database, generate changes script and execute this script.
Powershell script:

$Conn1 = New-DatabaseConnection -ServerInstance 'SQL' -Database 'DB1' -Username 'usr1' -Password 'pass1'

$Conn2 = New-DatabaseConnection -ServerInstance 'SQL' -Database 'DB2' -Username 'usr1' -Password 'pass1'

$Options = 'DecryptPost2KEncryptedObjects, DisableAndReenableDdlTriggers, -ObjectExistenceChecks, -ForceColumnOrder, IgnoreUserProperties, IgnoreUsersPermissionsAndRoleMemberships, IgnoreWhiteSpace, UseCompatibilityLevel, IgnoreDatabaseAndServerName, IgnoreIdentitySeedAndIncrement, IgnoreStatistics, IgnoreFillFactor, IgnoreFileGroups, IgnoreWithElementOrder'

$syncResult = Sync-DatabaseSchema -Source $Conn1 -Target $Conn2 -IgnoreAdditional -SQLCompareOptions $Options -AbortOnWarningLevel None

DB1 is local database, DB2 is database from other server (different location, subsidies). Logins and users are different in DB1 and DB2. When execute Powershell script get errors:

WARNING: The error 'Windows NT user or group 'DOMAINDB1\administrator' not found. Check the name again.' occurred when executing the following SQL:

WARNING: IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'DOMAINDB1\administrator')

WARNING: CREATE LOGIN [DOMAINDB1\administrator] FROM WINDOWS

Sync-DatabaseSchema : Windows NT user or group 'DOMAINDB1\administrator' not found. Check the name again.

Local parameters [connectionString = Data Source=SQL;Initial Catalog=DB1;User ID=usr1;Password=********;Applicatio

n Name="SQL Change Automation"]

How to exclude logins from compare?

Thank you.
Regards

Tianjiao_Li

You can create a compare filter file to exclude the users https://documentation.red-gate.com/sca/developing-databases/concepts/filtering

Tianjiao_Li

I'm afraid filter can't be used with -IgnoreAdditional switch.

​https://documentation.red-gate.com/sca/reference/powershell-cmdlets/sync-databaseschema

Once I removed it from the script, it appears to work fine for me. Please give it a try.

dUros

Hello

Thank you for your post.

I prepare Filter.scpf file with SQL Compare then modified my script:
....

$FilterPath = 'D:\Filters\Filter.scpf '

$syncResult = Sync-DatabaseSchema -Source $Conn1 -Target $Conn2 -FilterPath $FilterPath -IgnoreAdditional -SQLCompareOptions $Options -AbortOnWarningLevel None -QueryBatchTimeout 60

But now get this error:

SQLCompare.exe terminated with the exit code 8: Unsatisfied argument dependency. 

Either an argument was missing, or two incompatible arguments were used. 

Call the command with a valid set of arguments.

I try -FilterPath 'D:\Filters\Filter.scpf' and -FilterPath $FilterPath but no difference.

I looking documentation but can't find where I made a mistake.

Sync-DatabaseSchema -Source <Object> -Target <DatabaseConnection> [-FilterPath <string>] [-IgnoreStaticData] [-IgnoreAdditional] [-SQLCompareOptions <string>] [-SQLDataCompareOptions <string>] [-TransactionIsolationLevel <TransactionIsolationLevel>] [-QueryBatchTimeout <int>] [-AbortOnWarningLevel <WarningSeverity>] [-IgnoreParserErrors] [-SqlCmdVariables <hashtable>] [<CommonParameters>]

Do you have some example with -FilterPath argument?

Thanks again.

dUros

Hello

Thank you very much for your help and time.
I didn't see highlighted sentence  though I read documentation a few time. Now comparison work very well. 

Regards