I have run into this problem a couple times now:
The problem: Using a Visual Studio database project, I want to ignore users and roles on deployment AND keep the "Generate Drop statements for objects that are in the target database but that are not in the database project" option checked.
There is no configurable way of doing this during the deploy with VS2010 (and earlier). Please note that that is my post above and I am note shamelessly plagiarizing someone else's work!
A little background: I am a consultant and have introduced this tool (visual studio database projects) to several customers. Developers tend to like it and DBAs are on the wall (at best). The DBAs don't care how developers create sprocs, tables, indexes and such but they want absolute control over users and roles and I frankly don?t blame them. While I would certainly like to see this capability added, here is what I have done to deal with users and roles (bare in mind for this scenario I need to use a deploy, schema compare does offer some nice alternatives if that is not the case):
The environment: We have a development, test, and production environment. We (QA and developers) prove things out in the dev environment, we promote the code and DB changes to Test for more QA and BA scrutiny, and when all tests have passed we promote to production. This is a very typical process I see from customer to customer.
Developers have full access to the dev environment, limited (read only) access to test, and no access to production. In other words, each environment has a different set of users and roles but we, of course, want to use the same database project for each environment. Oh and we do want to use the "generate drop statements" option to help manage our changes (dropping a sproc, index, whatever...).
The solution: Because we want to make the production script as clean as possible we synch the database project with a copy of the production database minus any confidential information (which is not trivial to do). The project, therefore, contains all of the production users and roles (but none of the development and test users or roles). If the production users tend to be static, deploying changes to production will create a script without create user and modify role statements which is something the DBAs demand of the production update scripts. If the users and roles in production are more dynamic we have to either work with the DBA to manage user/role scripts in the project at the same time they are changed in production (assuming they prefer to add users without using Visual Studio) or we have to add a step to our process to synch the users and roles with production before we create the final deploy to production script. Using this process means our deploy to production scripts are nice and clean and void of any user and role changes - our primary goal is satisfied.
The down side of synching to production is that the development or test deploy script will generate "create user" and add rolemember statements for production users that we don?t want in the database and it will generate drop user statements for the users we do want in the dev and test environment . Remember the users we have in the project only map to the users in production. Well this is not the end of the world; it just means that we have some post deployment work to do. We add a post deploy script to the project that is responsible for dropping all (production) users that the script created for us and adding the dev or test users and role membership.
The script we deploy to say dev might look something like this:
USE [$(DatabaseName)]
GO
Pre-Deployment Script Template
...
The VS generated script
CREATE USER [domain\ProdUser1] FOR LOGIN [domain\ProdUser1];
GO
PRINT N'Creating [domain\ProdUser1]...';
GO
CREATE USER [domain\ProdUser2] FOR LOGIN [domain\ProdUser2];
GO
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\ProdUser1';
GO
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\ProdUser2';
GO
DROP USER [domain\Dev1];
GO
DROP USER [domain\Dev2] ;
GO
Alter table, drop sproc...
GO
Post-Deployment Script
IF (@@servername = 'DevDBServerName' )
begin
CREATE USER [domain\Dev1] FOR LOGIN [domain\Dev1];
CREATE USER [domain\Dev1] FOR LOGIN [domain\Dev2];
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\Dev1';
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\Dev2';';
EXECUTE sp_addrolemember @rolename = N'DataWriter', @membername = N'domain\Dev1';
EXECUTE sp_addrolemember @rolename = N'DataWriter', @membername = N'domain\Dev2';';
...
end
IF (@@servername = 'TestDBServerName' )
begin
CREATE USER [domain\Dev1] FOR LOGIN [domain\Dev1];
CREATE USER [domain\Dev1] FOR LOGIN [domain\Dev2];
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\Dev1';
EXECUTE sp_addrolemember @rolename = N'DataReader', @membername = N'domain\Dev2';';
...
end
I would love to hear how others manage this problem, drop me a comment if you are willing to share.
604055e7-dc33-428b-8f59-ca07171b6eea|1|5.0