Is it possible to exclude objects/object types from sqlpackage?

Question

I would like to exclude certain object, for example all logins & users, from extract or publish operation of sqlpackage.exe.

This is possible from within Visual Studio, so I hope it is also possible from the sqlpackage.exe.

Or is it not possible?

The reason is that I would like to be able to auto-deploy to various environments/servers, where the logins & users are different.

NOTE: Logins & Users is only an example, the question is more general.

Shortening

A workaround is that in Visual Studio / SSDT you can use Schema Compare, and in the Options (Object Types tab) you can uncheck Permissions, Users, Role Mamberships. – Anew

Possible duplicate of Prevent dropping of users when publishing a DACPAC using SqlPackage.exe – Cribb

Answer 1

Your best bet at this point is to look at doing this in post-deploy scripts and excluding all logins/users from your projects. We have similar issues where each environment has a different set of logins/users and SSDT just does not handle this well out of the box. I've written about the process we use on my blog (borrowed heavily from Jamie Thomson).

http://schottsql.blogspot.com/2013/05/ssdt-setting-different-permissions-per.html

I'll also note that the user "pavelz" left a comment briefly describing the process they use w/ composite projects - main project for objects and sub-projects for permissions. That could work as well.

The only issue we have run into with the post-deploy process is if you enable publishing to drop permissions/logins not in the project, you could have some down time until you re-add the permissions at the end. Once set, I highly recommend turning off those options.

Engrail

Thank you, but if I exclude all users then I must also exclude all privileges/grants prom the projects, no? – Shortening

I would really like to put grants under version control also. – Shortening

They would be in source control - just as post-deploy scripts instead of in a "permissions" file. Arguably, they might even be easier to read, though I haven't kept up too much with the "permissions" file details because we have a strong need for different permissions for different environments. – Engrail

OK, but that would mean manual maintenance of those scripts, right? – Shortening

It's actually not too bad once you have them generated but it really helps if you can grant permissions to a role as opposed to individual users. If you want, you can store those in the project or even grant things at the Schema level using something like "GRANT select ON Schema::dbo to RoleName" Either way, you'll have to handle permissions. If you choose to do the role option, you can have that as a shared type of post-deploy file that runs for all environments. – Engrail

what to do if the users which are required to be added are Azure Active Directory users using sqlpackage.exe? – Dux

Considering that the only way to add AAD users is with an AAD user, that might have to be done after the publish. It might be possible through an Azure Pipeline, but I haven't played with those as much through those scripts. It might be possible through PowerShell with the sqlserver module and an AAD credential to run that script. I'd probably start with Role assignments and then work on getting users into the roles. – Engrail

Answer 2

I solved this problem by creating a DeploymentPlanModifier contributor (following their SchemaBasedFilter sample) that I pass-in through in an argument (/p:AdditionalDeploymentContributors) for SQLPackage.exe, it looks for any drop operations of security object types.

(Code on Prevent dropping of users when publishing a DACPAC using SqlPackage.exe)

Radman

A nice implemenation to plug into that is /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor` – Refectory

Answer 3

It is now. Please update the tools and look at this post.

http://blogs.msdn.com/b/ssdt/archive/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop.aspx

Neufer

This link is broken with a 404 error. – Mayhew

Answer 4

Sadly, as of now sqlpackage.exe utility does not have any option of excluding a specific object. However, it does have options to exclude an entire object type.

Chaffinch

can we exclude a specific table/column from DACPAC CD deployment in azure – Larghetto

Answer 5

All of the same options available inside Visual Studio can be used in SqlPackage.exe. See "Publish Parameters, Properties and SQLCMD variables" in the documentation for a full list of options you can pass. They generally look like "/p:IgnoreUserSettingsObjects=True" and are passed alongside the regular arguments when calling SqlPackage.

Uneasy

I know there are parameters.. but, what parameter(s) are you saying I should use in this case? – Shortening

There are no parameters to do what you're trying to do in this case. – Engrail

That's what I feared.. :( – Shortening

@Shortening ExcludeObjectTypes will completely ignore a semi-colon delimited list of object types (i.e. it will neither publish, nor drop them). DoNotDropObjectTypes will not drop a semi-colon delimited list of object types. From this link already on this page blogs.msdn.com/b/ssdt/archive/2015/02/23/… This example: SqlPackage.exe /a:publish /tcs:"Data Source=localhost;Initial Catalog=mydb;Integrated Security=true" /sf:C:\temp\mydb.dacpac /p:ExcludeObjectTypes=RoleMembership;users – Popup

@Shortening Check out my response in https://mcmap.net/q/550612/-prevent-dropping-of-users-when-publishing-a-dacpac-using-sqlpackage-exe. There's all the required parameters for this task. – Cribb

Thank you. This is no longer an issue for me. – Shortening