Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


93
0
Sign in
to vote
Type: Suggestion
ID: 775839
Opened: 1/4/2013 2:03:13 PM
Access Restriction: Public
1
Workaround(s)
view

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
Details
Sign in to post a comment.
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 Microsoft 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
Sign in to post a workaround.
Posted by Buddhatripp on 1/29/2013 at 10:51 AM
The workaround we use is to disable the "Drop Objects Not In Source" option and perform a schema compare against a master database instance (fresh database with the recreate option turned on) then perform the drops in our continuous integration process.

Another approach is to use a pre-deployment script to perform the drops. Just check for existence of the object before executing the drop just like you would do before SSDT came along.