9

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.

SteveC
  • 15,808
  • 23
  • 102
  • 173
SAS
  • 3,943
  • 2
  • 27
  • 48
  • 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. – rohancragg Sep 26 '14 at 13:52
  • Possible duplicate of [Prevent dropping of users when publishing a DACPAC using SqlPackage.exe](http://stackoverflow.com/questions/17402904/prevent-dropping-of-users-when-publishing-a-dacpac-using-sqlpackage-exe) – Tuukka Haapaniemi May 11 '17 at 12:57

5 Answers5

6

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

Derek Smalls
  • 194
  • 2
  • 4
5

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)

Community
  • 1
  • 1
DanStory
  • 371
  • 2
  • 6
  • A nice implemenation to plug into that is /p:AdditionalDeploymentContributors=[AgileSqlClub.DeploymentFilterContributor](https://github.com/GoEddie/DeploymentContributorFilterer)` – CrazyPyro Jul 17 '19 at 02:43
4

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.

Peter Schott
  • 4,521
  • 21
  • 30
  • Thank you, but if I exclude all users then I must also exclude all privileges/grants prom the projects, no? – SAS Jan 27 '14 at 06:42
  • I would really like to put grants under version control also. – SAS Jan 27 '14 at 07:10
  • 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. – Peter Schott Jan 27 '14 at 17:42
  • OK, but that would mean manual maintenance of those scripts, right? – SAS Jan 28 '14 at 08:12
  • 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. – Peter Schott Jan 28 '14 at 17:35
  • what to do if the users which are required to be added are Azure Active Directory users using sqlpackage.exe? – Prashant Babber Jan 22 '21 at 11:17
  • 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. – Peter Schott Jan 23 '21 at 15:05
2

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.

shilpa
  • 31
  • 4
1

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.

Kevin Cunnane
  • 8,020
  • 1
  • 32
  • 35
  • I know there are parameters.. but, what parameter(s) are you saying I should use in this case? – SAS Jan 27 '14 at 06:41
  • 1
    There are no parameters to do what you're trying to do in this case. – Peter Schott Jan 27 '14 at 17:41
  • That's what I feared.. :( – SAS Jan 29 '14 at 06:40
  • 3
    @SAS 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 http://blogs.msdn.com/b/ssdt/archive/2015/02/23/new-advanced-publish-options-to-specify-object-types-to-exclude-or-not-drop.aspx 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 – Davos Dec 01 '15 at 00:27
  • 2
    @SAS Check out my response in http://stackoverflow.com/a/43916061/1328993. There's all the required parameters for this task. – Tuukka Haapaniemi May 11 '17 at 12:58
  • Thank you. This is no longer an issue for me. – SAS May 11 '17 at 13:00