Home Dashboard Directory Help

sqlpackage.exe Exclude objects types from schema comapre by Kathir vel



Sign in
to vote
Type: Bug
ID: 795667
Opened: 8/1/2013 4:49:28 AM
Access Restriction: Public
User(s) can reproduce this bug


I'm trying to automate our DB deployments using sqlpackage.exe. when i do the /a:Script option its comparing User also. I need to exclude some object types like Users, Database Roles, Permissions, etc.

Please help me to find out the correct parameter option. SqlPackage Options

Its possible when i use SSDT

/p:IgnorePermissions and /p:IgnoreRoleMembership options are not working.

Sign in to post a comment.
Posted by Brett Gerhardi(H) on 7/4/2014 at 9:31 AM
The case that I have is that my users are tied to logins and those logins are Active Directory and they change from environment to environment (dev/test/live etc) as would be common.

As you can't specify the login/user name with a setvar (which could be a solution to this issue) you cannot include them as objects in the ssdt build.

Because a user must have the same name as AD login, this means you cannot include the users either.

However I do have a user with no login that is used for service broker activation SP. I think that because I have one User and I do want to drop objects that are not in my project, SSDT then decides that I'm managing Users in the system and drops all of them. As a result I have a complex set of sqlcmd scripts in pre/post deploy to manage these correctly... and still even if it is a deploy that just adds a single new stored procedure, SSDT deploy drops all my users which then need to be recreated causing an interruption in service.

Ultimately I'm happy to just have database objects in the schema but that should include Users, Roles, Permissions (to users and roles), Schemas. But not Logins. Ideally though I would have Logins/Linked Servers and everything in there. but in practice these do not work and it doesn't seem that Microsoft is interested in making them work in real life scenario's.

So, I do want to have users deployed that are in the project but not drop those that aren't in the project and I want this to be independent of the other settings in publish profile..
Posted by mistermanager on 12/19/2013 at 7:03 AM
We are experiencing the same issue where I work. The SSDT schema compare tool has this functionality however no deployment providers also have this. It doesn't HAVE to be at the same granularity to SchemaCompare allows (although that would be preferred) but perhaps if the DropObjectsNotInSource flag could be split into DropSecurityObjectsNotInSource, DropSchemaObjectsNotInSource, ETC.....

The IgnorePermissions=true and DropPermissionsNotInSource=False settings will only work "as expected" if the user exists in both the source AND target (unless of course you turn DropObjectsNotInSource to false, defeating the purpose of the tool)... this is not intuitive. Therefore the ability to ignore users or specific object types is invaluable.
Sign in to post a workaround.