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]
2018-07-11T14:26:35.2840935Z ##[debug]Finished batch 9 of 11 (batch took 0.027 seconds)
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:
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:
2018-07-11T14:26:35.2850632Z DROP USER [Deploy]</div>

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

Serge

chris_godfree

Hi Serge, the deployment is doing a comparison of objects in your source control and your target database so this suggests that the user is not in source control (correctly in my view) so will want to drop it from your target database. You can set up a filter in SQL Source Control to exclude all users (or just this user) and the deployment won't try to drop the user.

Search

Hi Chris, thanks for your answer. I have added the user to source control, that's why I don't understand why it would want to delete it. I though about adding it to the filters so it's ignored, but then I would have to create it manually on every environment. I guess I'm just looking for a best practice here. How have you setup your deployment?

chris_godfree

Strange that it is trying to drop the user in that case, have you checked what differences are being identified if you use SQL Compare - useful to get a different view of things when there's a problem like this.

We have created AD accounts for each environment we deploy to (Test, PreProd, Prod) and set them up as the user for TFS Agent Queues to run under. In our TFS database releases, we use the relevant queue for the environment and grant the AD account relevant permissions on the database(s) in that environment so we're using Windows Authentication throughout. No-one can therefore use the Test TFS Agent Queue to deploy to a Production database.

We exclude all users from source control using a filter so we don't get into the situation of having a Development user deployed to a Production database when it shouldn't be. If we need to amend users/permissions then this needs to be scripted separately - we find this an easier solution than trying to somehow deploy specific users to specific environments.

Search

That's a good point, I'll make a diff. 
Thank you for the detailed description. That does sound like the better option. I've read a few times of people using an AD user instead of a SQL user, but never really saw the point why. Also excluding them makes sense. I will give that a try. Thanks again!