Creating package fails because user deletes itself in temporary database

• 
  Hi @LouisSomers,
  
  Does the user account which the build is running under have db_owner permission for the database? 
  
  Here is our guidance on permissions for the build with the different options: https://productsupport.red-gate.com/hc/en-us/articles/360016293733-SQL-Change-Automation-Powershell-permissions
  
  Hope this helps,
  Kendra

  Kendra_Little March 02, 2021 13:40

  - 0 +
• 
  Hi @Kendra_Little, thank you for your fast response,
  
  

  Yes the user had db_owner rights in the first example.

  In the 2nd example the user also had db_owner rights but then I explicitly checked "Deny" on "Alter any role" and "Alter any user" in the database properties -> permissions.

  The user only has db_owner rights on that specific database, not on the whole server.
  

  LouisSomers March 02, 2021 13:51

  - 0 +
• 
  Maybe it will help if I provide a larger portion of the log, there's also a stack trace in there...

  The first part is successful, it runs SQLCompare.exe without any issue, the problems only start after it deletes the user it is logged in with. This is a domain user by the way.

  
  

  VERBOSE: C:\**TeamCityPath**\BuildAgent\plugins\sqlchangeautomation-teamcity-agent\Modules\SqlChangeAutomation\4.3.21056.24410\SC\SQLCompare.exe ended with exit code 79
  VERBOSE: SQLCompare.exe completed successfully.
  VERBOSE: Altering schemas
  VERBOSE: Dropping users
  WARNING: The error 'The server principal "**DOMAIN**\**USERNAME**" is not able to access the database "**DBNAME**" under the current security context.' occurred when executing the following SQL:
  @ERROR <> 0 SET NOEXEC ON
  WARNING: The error 'The server principal "**DOMAIN**\**USERNAME**" is not able to access the database "**DBNAME**" under the current security context.' occurred when executing the following SQL:
  DROP USER [**DOMAIN**\**other_user**]
  WARNING: The error 'The server principal "**DOMAIN**\**USERNAME**" is not able to access the database "**DBNAME**" under the current security context.' occurred when executing the following SQL:
  @ERROR <> 0 SET NOEXEC ON
  WARNING: The error 'The server principal "**DOMAIN**\**USERNAME**" is not able to access the database "**DBNAME**" under the current security context.' occurred when executing the following SQL:
  DECLARE @Success AS BIT
  SET @Success = 1
  SET NOEXEC OFF
  IF (@Success = 1) PRINT 'The database update succeeded'
  ELSE BEGIN
  @TRANCOUNT > 0 ROLLBACK TRANSACTION
   PRINT 'The database update failed'
  END
  Invoke-DatabaseBuild : Failed to clean the database '**DBNAME**' on server '**DBSERVER**'.
  Error: 4 batches failed. The first error is 'The server principal "**DOMAIN**\**USERNAME**" is not able to access the database "**DBNAME**" under the current security context.'
  Stack Trace:    at RedGate.Versioning.Automation.Compare.SqlProcessing.SqlExecutor.ExecuteBatches(IHaveConnectionString connectionString, IReadOnlyCollection`1 sqlBatches, Int32 commandTimeout, Action`1 logAction)
     at RedGate.Versioning.Automation.Compare.Compare.SchemaUpdater.Clean(ExistingDatabase database, CompareOptionArguments compareOptionArguments)
  Local parameters [connectionString = Data Source=**DBSERVER**;Initial Catalog=**DBNAME**;Integrated Security=True;Pooling=False;Trust Server Certificate=True;Application Name="SQL Change Automation"]
  At C:\**TeamCityPath**\BuildAgent\plugins\sqlchangeautomation-teamcity-agent\SqlCi.ps1:193 char:39
  

  
  

  LouisSomers March 03, 2021 12:33

  - 0 +
• 
  The solution is to use the Filter.scpf file to exclude the user, and explicitly "override" the Filter.scpf file using the "Path to filter file" in the TeamCity build step. ("override" it with exactly the same file or else the filters will not apply at all).
  
  After that the user lives happily ever after!

  LouisSomers March 10, 2021 10:22

  - 0 +
• 
  @LouisSomers thank you so much for following up and posting the solution! 
  

  Kendra_Little March 11, 2021 09:25

  - 0 +

Add comment

Please sign in to leave a comment.