How do I ignore differences in user accounts while deploying a database using TFS Build against SQL 2008?

Question

I have run across a scenario where I am trying to troubleshoot something I did not originally create so I apologize in advance if some key data is missing.  I am still gathering info myself.  I'll provide as much as I can and update as needed...  

In this scenario there is a team build script which is updating 450 identical databases across 7 servers (I know, ACK, that is another issue we are addressing long term).  There is a master table of databases and servers that is parsed by team build to create scripts that will apply database updates on all the servers at once.  So 7 deployment scripts are created then the appropriate one is dropped on each server, and executed so that more or less all servers get updated at roughly the same time. Go team build!

The problem is that there is a user that exists on all server machines, but did not exist locally for the developer who created the script (at least that is my guess, that developer is not around anymore for me to ask).  So it looks like it was being created locally as part of the database project, but this is where my newness to SQL Server shows because I am not entirely clear on this yet. When I open the local database project I see files under the database project that create the user (under Security | Users and Security | Schema).  Because of the way it is created (more info coming soon), it is seen as NEW every time we do a database deploy and the user gets dropped and re-added EVERY TIME.  This is an issue because other things depend on that user, and so updating the database causes a lot of other apps to fail. 

What I see in the local database project for the database security Schemas is a script that reads: "CREATE SCHEMA [User] AUTHORIZATION [User];", and under Security Users  is a script that reads: “CREATE USER [User] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [User];”.

From what I have read about SQL users, it appears this is because the user exists on the target servers that the project was originally reverse engineered from, and did not exist on the local server. So this script creates the user locally so permissions can be parsed from the .sqlpermissions file, but that user does not get the server permissions.

So, I believe this is why the user keeps getting dropped and recreated during the database deploy.  I am not sure how to turn that off so that the user would be ignored as part of the incremental database update.  Can I?  I've seen Barclay Hill reply to a few posts saying you can't, but it seems like there must be a workaround, this cannot be a scenario that only I am running into.  It’s seems I can tell schema compare to ignore users during the compare operation when creating the script LOCALLY, but I am not sure how to do that during the DB deploy. 

It could be that ideally there is a better way to do this, but right now I just need to fix their immediate need which is to stop the automated database update from blasting away that user. 

---

Angela Dugan

Answer 1

Hi Angela,

Please correct me if I was wrong. Maybe you can ignore users when you deploy a database project by setting the Build Action property to "Not In Build" for these .user.sql files.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us