Ignore User On Deployment

Question

Is it true that you cannot ignore users 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?

 

 

 

---

Tim

Answer 1

Hi Tim, yes, this is true.  There are deployment options that allow differences to be ignored when the deployment comparison is run for some objects, but the existence of the objects in the source and target is absolute in regards to the Generate Drop Statements... deployment option.

Thanks,

---

Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill

Answer 2

Thanks Barclay,

I thought that was the answer but I did see one post by you and one post by Gokhan Caglar that suggested there was some wiggle room.

I would certainly like to see this capability added.  I am a consultant and have introduced this tool to several customers.  Developers tend to like it and DBAs are on the wall.  The DBAs don't care how developers create sprocs, and tables and such but they want absolute control over users and roles.   I would love to see how others manage this problem.  For anyone interested here is what I have done to deal with users and roles (bare in mind I need to use a deploy, schema compare does offer some nice alternatives):

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...).

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 in production. 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 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 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 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';
..
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

---

Tim

Answer 3

Just wanted to state that I would also like to see this capability added for the exact same reasons.

---

steve

Answer 4

Bumping an old thread to just give it a +1.  We are currently fighting this problem as well.  Would love to turn on GenerateDrops but we can't because we lose all of our users assigned by our Ops team in the production database.  Why can't we have an IgnoreUsers option?  It looks like we have Ignores for everything else.

Answer 5

the normal way to handle is by creating a srever project with thos login object in it and just referencing it your active project and by not droping everything...

if you drop and table, sp, etc... you automatically loose the rights associated with the object

if a table has specific rights assigned and you dont want the database project to keep track those rights you should be altering the table, not droping it

one solution would be to create a SP that would generate a script to recreate user rights withing the db...

1) then as a procedure you would run that SP first to generate the script to replace user rights
2) drop your objects
3) recreate objects
4) run the generated script from step 1 to recreate the rights

otherwise... tell the DBA's that want control that they have to deal with it.

normally this should be done on the developers end whose designing an app and the database that goes with it. security should be integrated on both sides and they need to match!

---

CJ

Answer 6

Nope, sorry...

Microsoft should just give us an option to "Ignore Users" so that we can enable the "Generate Drop statements for objects that are in the target database but that are not in the database project" without dropping the users.

Anything else sucks as a work around and is not worth the effort it takes to enable the "Generate Drop statement" feature.

I can tell DBA's they have to deal with it all day long. Then they can laugh and say no, sorry, you deal with it. Frankly, I agree with the DBA's anyway, I do not want developers handling who has access to production databases.

---

steve