Alexander Kuznetsov (6/25/2009)
I want the opposite - I want security on test boxes to closely match production security, and I want no hassle at all. That's what I use roles for. Also I absolutely do not want to give write access to my solution to the people who actually grant roles to groups.
Exactly! We use roles for similar results, but our security is a bit different than yours. So, if you want to also deploy the logins and users with VSTS:DB, you'll have to define them in scripts. But, if you define them in the standard scripts, they won't resolve correctly when the engine checks the syntax. You create a server project where you place the logins, and associate that server project with your database project and then the scripts will resolve. Then, you need to take into account that the same database that you're deploying in dev, that has, I assume, two sets of behaviors, a straight out of production copy, so that developers can write code against it, and a developers copy that allows them to do whatever you allow developers to do (in our case, create procs, but not tables or indexes, or views, etc.). Now you have to have a method for deploying to to places, with two different sets of security.
That's why we're doing the stuff we're doing with the compound projects and the configurations.
It's not for everyone, certainly. But if you find yourself in similar circumstance, and you need to be able to repeatedly, accurately, quickly, do lots of deployments to lots of environments, I haven't found any methods or tools that work quite as well as what we have now.
It's not the only way to skin the cat. Previous to the GDR release, we added post-deployment scripts to the projects. In these we would put a query that tested the server where the script was being run, by name. We would create appropriate security through these scripts. That approach is a little less intrusive than the method I've outlined here, but it's also MUCH less visible. That invisibility caused a lot of failed deployments. People would assume a particular system had been set up and deploy the database, only to have security fail. This approach requires only a little more work than the other one, but it's very visible. So you know whether or not a given server or environment has been set up for deployment.