Why does TFS deployment with Red Gate plugin want to drop own user?

Search

Hello
we are deploying our database changes with Red Gate source control through TFS and the provided Red Gate plugin. To get everything setup quickly we have been using the sa user for deployment, but I would like to get away from that so I created an individual deploy user (this is a SQL Server Authentication user). Now when I use that user for the deployment the deployment fails because it wants to drop that user. What could cause that? Why would it even want to do that? Here's the batch script and error messages:

<div>2018-07-11T14:26:35.2570610Z ##[debug]Starting batch 9 of 11 (0.006 seconds since script started): DROP USER [Deploy]
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">2018-07-11T14:26:35.2840935Z ##[debug]Finished batch 9 of 11 (batch took 0.027 seconds)
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">2018-07-11T14:26:35.2850632Z ##[warning]The error 'The server principal "Deploy" is not able to access the database "MyDb" under the current security context.' occurred when executing the following SQL:
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">2018-07-11T14:26:35.2850632Z ##[debug]Processed: ##vso[task.logissue type=warning]The error 'The server principal "Deploy" is not able to access the database "MyDb" under the current security context.' occurred when executing the following SQL:
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">2018-07-11T14:26:35.2850632Z DROP USER [Deploy]</span></div>

It makes sense that it fails. I'm just trying to understand why it even wants to drop the user.

Serge

Sergio R

Hi Serge,

I assume that you are using MyDb as the Temporary Database.

Before validating the scripts folder in the Temporary Database, DLM Automation\SQL Change Automation will clear the database.

To avoid that user being dropped at this stage you should use a filter to exclude that user and then specify the path to that filter on the build definitions in TFS: