Creating package fails because user deletes itself in temporary database

LouisSomers

I asked this question here before, but it disappeared after an edit (changed the log style from code to quote).

Using the TeamCity integration step "Redgate SQL Change Automation Build", I provided a temporary database name.

The helpful text under the field states: "Enter the name of an existing database for SQL Change Automation to use when building your database on the server.".

The build fails and in the logs I see that the user is deleting itself:

VERBOSE: SQLCompare.exe completed successfully.
VERBOSE: Altering schemas
VERBOSE: Dropping users
WARNING: The error 'The server principal "**server\username**" is not able to access the database "**dbName**"
under the current security context.' occurred when executing the following SQL:

IF @@ERROR <> 0 SET NOEXEC ON

I added the user again, and revoked the rights to change users and groups, after that the logs sayed:

VERBOSE: SQLCompare.exe completed successfully.
VERBOSE: Altering schemas
VERBOSE: Dropping users
WARNING: The error 'Cannot drop the user '**server\username**', because it does not exist or you do not have permission.' occurred when executing the following SQL:
DROP USER [**server\username**]
VERBOSE: The database update failed
Invoke-DatabaseBuild : Failed to clean the database '**dbName**' on server '**servername**'.

I already added filters for users and roles in the Filter.scpf file, but that does not seem to have any effect.

Any therapy that I can apply to this suicidal user?

LouisSomers

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!

Kendra_Little

@LouisSomers thank you so much for following up and posting the solution! 

Kendra_Little

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

LouisSomers

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

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