DLM Automation sync error (Trying to drop the user)

Ravin

Hi,

When I try to sync the nugget repository with Target Server, it is dropping the user from target db and throwing an error.

Could any one help me in disabling the users drop from target db please?

build 23-Aug-2017 14:55:21 VERBOSE: Calling: C:\Program Files (x86)\Red Gate\DLM Automation 2\SC\SQLCompare.exe
build 23-Aug-2017 14:55:21 /options:ConsiderNextFilegroupInPartitionSchemes,DecryptPost2kEncryptedObjects,DoNotOutputCommentHeader,ForceColumnOrde
build 23-Aug-2017 14:55:21 r,IgnoreCertificatesAndCryptoKeys,IgnoreDatabaseAndServerName,IgnoreTSQLT,IgnoreUserProperties,IgnoreUsersPermissionsAn
build 23-Aug-2017 14:55:21 dRoleMemberships,IgnoreWhiteSpace,IgnoreWithElementOrder,IncludeDependencies,ThrowOnFileParseFailed,UseCompatibilityLev
build 23-Aug-2017 14:55:21 el /transactionIsolationLevel:SERIALIZABLE /include:staticData
build 23-Aug-2017 14:55:21 "/scriptFile:C:\Users\BambooZ01\AppData\Local\Temp\DLM Automation\obdxmtaw.sql" /showWarnings /include:Identical
build 23-Aug-2017 14:55:21 "/report:C:\Users\BambooZ01\AppData\Local\Temp\DLM Automation\tmipdri4.xml" /reportType:Xml /assertidentical /force
build 23-Aug-2017 14:55:21 /OutputWidth:1024 "/scripts1:C:\Users\BambooZ01\AppData\Local\Temp\DLM Automation\nkw0iwxb.maq\db\state"
build 23-Aug-2017 14:55:21 /server2:pd52vdbaz02.dev.com /database2:DataHub_Dev /out:"C:\Users\BambooZ01\AppData\Local\Temp\DLM
build 23-Aug-2017 14:55:21 Automation\hc50atxk.log"
build 23-Aug-2017 14:55:26 VERBOSE: C:\Program Files (x86)\Red Gate\DLM Automation 2\SC\SQLCompare.exe ended with exit code 79
build 23-Aug-2017 14:55:26 VERBOSE: SQLCompare.exe completed successfully.
build 23-Aug-2017 14:55:26 WARNING: (Low) Dropping a user is a potentially dangerous operation. Dependencies for the DEV\BambooZ01 user are
build 23-Aug-2017 14:55:26 not selected automatically. If you are sure you want to drop this user please select the objects that belong to this
build 23-Aug-2017 14:55:26 user manually.
build 23-Aug-2017 14:55:26 VERBOSE: Dropping users
build 23-Aug-2017 14:55:26 WARNING: The error 'The server principal "DEV\BambooZ01" is not able to access the database "DataHub_Dev" under
build 23-Aug-2017 14:55:26 the current security context.' occurred when executing the following SQL:
build 23-Aug-2017 14:55:26
@ERROR <> 0 SET NOEXEC ON
build 23-Aug-2017 14:55:26 WARNING: The error 'The server principal "DEV\BambooZ01" is not able to access the database "DataHub_Dev" under
build 23-Aug-2017 14:55:26 the current security context.' occurred when executing the following SQL:
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 DECLARE @Success AS BIT
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 SET @Success = 1
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 SET NOEXEC OFF
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 IF (@Success = 1) PRINT 'The database update succeeded'
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 ELSE BEGIN
build 23-Aug-2017 14:55:26
@TRANCOUNT > 0 ROLLBACK TRANSACTION
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 PRINT 'The database update failed'
build 23-Aug-2017 14:55:26
build 23-Aug-2017 14:55:26 END
build 23-Aug-2017 14:55:26 Sync-DlmDatabaseSchema : 2 batches failed. The first error is 'The server principal "DEV\BambooZ01" is not able
build 23-Aug-2017 14:55:26 to access the database "DataHub_Dev" under the current security context.'.
build 23-Aug-2017 14:55:26 Local parameters [connectionString = Data Source=XXXXX;Initial Catalog=DataHub_Dev;Integrated
build 23-Aug-2017 14:55:26 Security=True;Application Name="Redgate DLM Automation"]
build 23-Aug-2017 14:55:26 At C:\Program Files (x86)\Red Gate\DLM Automation 2\sqlci.ps1:373 char:19
build 23-Aug-2017 14:55:26 + $syncResult = Sync-DlmDatabaseSchema @queryBatchTimeoutArguments `
build 23-Aug-2017 14:55:26 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
build 23-Aug-2017 14:55:26 + CategoryInfo : NotSpecified: (database 'DataH...z02.dev.com':DatabaseConnection) [Sync-DlmDatabaseSc
build 23-Aug-2017 14:55:26 hema], TerminatingException
build 23-Aug-2017 14:55:26 + FullyQualifiedErrorId : SqlScriptExecutionError,RedGate.DLMAutomation.PowerShell.Commands.SyncDlmDatabaseSchemaC
build 23-Aug-2017 14:55:26 ommand
simple 23-Aug-2017 14:55:28 Failing task since return code of ["C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -NonInteractive -ExecutionPolicy Bypass -File "C:\Program Files (x86)\Red Gate\DLM Automation 2\\sqlci.ps1" -Verbose Sync -package DataHub01.1.9.nupkg -databaseName DataHub_Dev -databaseServer XXXX] was 1 while expected 0

Andrew P

Hey Ravin,

I recommend using Filters in SQL Source Control to remove Users from the deployment so that they are not dropped - or at least filter out that specific User so that it can deploy successfully.

I hope that helps!