How can we help you today? How can we help you today?

Exclude Logins from SQL database compare

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
dUros
0

Comments

4 comments

Sort by
  • 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
    0
  • 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
    0
  • 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.

    Tianjiao_Li
    0
  • 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
    dUros
    0

Add comment

Please sign in to leave a comment.