Ignoring Users and Roles On Deployment using VS2010 DB Project

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.


Posted by: Tim Star
Posted on: 6/24/2010 at 5:27 PM
Tags: , ,
Categories: .NET | Visual Studio
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Subscribe to this BlogRSS comment feed

Add comment




biuquote
  • Comment
  • Preview
Loading


Contact Us 651-994-8558 1-800-866-9884
Home | Training | Curriculum | Course Finder | Schedule | Enroll | Twin Cities Java User Group | Consulting | Foundation | Jobs | About Us | Our Story | Press Room | Instructors | President | Map & Directions | Sitemap

Java Training | JSF / Struts / Spring / Hibernate Training | Java Power Tools Training | .NET 4.0 & Visual Studio 2010 Training | .NET 3.5 and Visual Studio 2008 Training | .NET 2.0 and Visual Studio 2003 Training | Prism / MVVM / MEF Training | Microsoft Web Development Training | Cloud Computing Training | Ajax / Web Services / XML Training | Groovy and Grails Training | SQL Server 2008 Training | SQL Server 2005 Training | Mobile Development Training | SharePoint 2010 Training | SharePoint 2007 Training | Agile, Process, Analysis & Design Training | Arch/Design Patterns Training | Microsoft Official Curriculum Training | Web Development Training | Ruby Training | Rational Application Developer (RAD) Training | WebSphere Application Server Training | WebSphere Portal Training | WebLogic Training | Boot Camp Training | Project Management Training | C++ Training | Metro / WinRT / Windows 8 Development Training | Retired

Intertech delivers training on-site and virtually serving cities including Phoenix, AZ | San Francisco, CA | Los Angeles, CA | San Diego, CA | San Jose, CA | Washington, DC | Chicago, IL | Orlando, FL | Boston, MA | Duluth, MN | Minneapolis St. Paul, MN | Rochester, MN | Raleigh-Durham, NC | New York, NY | Philadelphia, PA | Austin, TX | Dallas, TX | Houston, TX | Seattle, WA.