[SSDT] Add publication setting to "Ignore database users" - by eskarina

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


131
0
Sign in
to vote
ID 775839 Comments
Status Closed Workarounds
Type Suggestion Repros 2
Opened 1/4/2013 2:03:13 PM
Access Restriction Public

Description

Please consider adding a publish setting to "Ignore database users" similar to the existing "Ignore permissions" and "Ignore role membership" settings. Many of our servers & databases have different logins, users, & permissions in different environments. It's extremely challenging to use SSDT to publish to these environments without having to manually modify the publication scripts to clean up unnecessary security-related changes. 

Others reporting similar issues:

http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/1952c253-baf7-4186-844d-9507bce2299d

http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/00755c1e-3afd-40ee-b907-86d4c23355cc/

http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/8bf3d70a-60fd-400a-b4fa-8e11ab0d0ea0
Sign in to post a comment.
Posted by Steven [MSFT] on 2/24/2015 at 6:27 PM
Thank you all for the clear and consistent feedback you have provided on this issue. I'm very happy to be able to inform you that support for this feature will be arriving, soon, in an upcoming release of SQL Server Data Tools. For more details, please refer to http://blogs.msdn.com/b/ssdt/archive/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop.aspx

Thanks again,
Steven Green
SQL Server Data Tools team
Posted by DeveloperDBADeliveryJohn on 2/12/2015 at 7:33 AM
Having spent many years packaging and deploying databases in ERP systems (which tend to manage the objects themselves), I was anxious to find a method that would help me keep control of database deployments in stand-alone .NET applications built on SQLServer. I thought I'd found the answer with SSDT and DacPac deployment - being able to generate a deliverable file which SQLServer analyzes to make the right changes is exactly the ind of solution I wanted. BUT AS MANY OTHERS HAVE POINTED OUT, DEVELOPERS DO NOT ADMINISTRATE DATABASE SECURITY. It is simply unworkable to require that the development project includes all the database users in the production envinronment in order to prevent them from being dropped. PLEASE FIX THIS!

By the way, coding a means of recreating the users after the deployment has needlessly dropped them is hardly a workaround. The existence of such work-arounds is not a reason to ignore this huge weakness with an otherwise beautiful solution.
Posted by David G Boyle on 12/8/2014 at 5:12 PM
agree - critical. You can ignore users in schema compare, but not publish/SqlPackage?????
Posted by shauntck on 11/6/2014 at 11:56 PM
This is pretty critical guys... we need this addressed.
Posted by SQLEmil on 8/15/2014 at 1:45 PM
Adding an echo that it is unbelievable Microsoft is not going to address this. How is SQL Server development supposed to get taken seriously when Microsoft hamstrings all the tools that might make Continuous Delivery of SQL Server solutions anything less than painful.
Posted by iXquisite on 8/13/2014 at 5:37 PM
Can't believe MS simply declares this a "Won't fix!" and goes to rest. I am wondering if this is more of an admission down the lines of "We screwed up the foundation of SSDT. Can't fix!"
How can you ignore this when you are serious about Enterprise?
Posted by Ant-T on 4/24/2014 at 8:54 AM
I agree. This is a HUGE hole in the entire SSDT solution. I can't think of a scenario where Dev and Prod would have the same security.

EVERYTHING else is fantastic but this issue is a deal breaker.

Thanks.
Posted by AndrewKetley on 4/10/2014 at 8:47 AM
This should definitely be fixed. A deployment process which can lock developers out of the development database is just embarrassing.
Posted by LuisF77 on 10/15/2013 at 1:52 AM
Hey SSDT team... please fix this issue - it's a bug in your tool and if you want people to use your tool you need to fix it.
In my scenario (using sqlpackage.exe in the build/deployment server), the user context in which I do the publishing does not have permissions to DROP users logins etc.. so, my whole deployment breaks as soon as the script tries to drop a user (even tough I used the command-line options to ignore users)... It fails because I want to use /p:IncludeTransactionalScripts=True to have some warranty on rolling back if an issue occurred. Actually, had I not used /p:IncludeTransactionalScripts=True then all my users would have been dropped in the Production DB. Can you imagine what the implications are if that happened? People lose their jobs for less.
Posted by dbajonm_ on 9/10/2013 at 10:06 AM
I would like you to reconsider tabling of this item. I have worked up a similar pre/post deployment script as a workaround but the workaround does not fix our issue. Our production DBAs want to set up the security once and then leave it alone. This is ingrained to the extent that only the prod DBA managers have permission to drop/create users. We handle financial transactions so security is a key tenant and many of our maxims. The DACPAC drops users and then recreates them causing permissions violations whenever database changes are deployed to production environments. This is a fairly big deal for us as we are attempting to move our deployments to DACPACs.
Posted by Seant77 on 9/5/2013 at 10:10 AM
This feature is much needed. Users and roles in production databases are not managed by developers yet the rest of the schema is.
Posted by j. moblex on 1/22/2013 at 10:00 AM
So what is the workaround if Dev, Test, UAT, and Production have different database users?
Posted by Adam [MSFT] on 1/20/2013 at 8:00 PM
Hey eskarina,

Thanks for reporting this issue regarding a potential deployment option to ignore differences in users when publishing or deploying using SSDT/DACFx. The SSDT/DACFx team is not pursuing a fix for this issue at this time, but we do have an item tacking the issue should it come up for consideration in the future.

Thanks,
Adam Mahood
Program Manager
SQL Server Database Systems