CODEWITHSUNDEEP
sql-serverazure-devopsazure-pipelinesdacpacsqlpackage
dtc
dtc

Reputation: 185

Deploy DACPAC with SqlPackage from Azure Pipeline is ignoring arguments and dropping users

I have a release pipeline in Azure where I pass a .dacpac artifact (created with VStudio) to be deployed in an on-prem Sql Server 2016. It works good, but now I want to drop tables, views, functions and stored procedures on my server via .dacpac. So I did that in my VS project and then tried to deploy with option /p:DropObjectsNotInSource=True along with /p:DoNotDropObjectType and even /p:ExcludeObjectType to exclude the things that I didn't want to be dropped.

But regardless of these filters, once the job starts, the tables are dropped (as expected) but then it drops the DB users. It also removes the db_owner mapping of the login that I used to install the Azure agent and then try to drop that login (which is the same user that is configured to authenticate during the deploy). Then it fails with this error:

Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the user '<Domain>\<User>', because it does not exist or you do not have permission.

Which is obvious since the user's permission to the DB was just removed. Any suggestions to avoid this, or my only choice is to re-create the users/permissions once finished? Or not to use that flag altogether and do all the "drop objects plumbing" via post-deployment script?

For reference, the additional arguments set on my release task are (it looks bizarre but there is no way to specify ONLY the objects that I want to drop):

/p:DropObjectsNotInSource=True /p:BlockOnPossibleDataLoss=false /p:DoNotDropObjectType="Aggregates,ApplicationRoles,Assemblies,AsymmetricKeys,BrokerPriorities,Certificates,ColumnEncryptionKeys,ColumnMasterKeys,Contracts,DatabaseRoles,DatabaseTriggers,Defaults,ExtendedProperties,ExternalDataSources,ExternalFileFormats,ExternalTables,Filegroups,FileTables,FullTextCatalogs,FullTextStoplists,MessageTypes,PartitionFunctions,PartitionSchemes,Permissions,Queues,RemoteServiceBindings,RoleMembership,Rules,SearchPropertyLists,SecurityPolicies,Sequences,Services,Signatures,SymmetricKeys,Synonyms,UserDefinedDataTypes,UserDefinedTableTypes,ClrUserDefinedTypes,Users,XmlSchemaCollections,Audits,Credentials,CryptographicProviders,DatabaseAuditSpecifications,DatabaseScopedCredentials,Endpoints,ErrorMessages,EventNotifications,EventSessions,LinkedServerLogins,LinkedServers,Logins,Routes,ServerAuditSpecifications,ServerRoleMembership,ServerRoles,ServerTriggers" /p:ExcludeObjectType="Aggregates,ApplicationRoles,Assemblies,AsymmetricKeys,BrokerPriorities,Certificates,ColumnEncryptionKeys,ColumnMasterKeys,Contracts,DatabaseRoles,DatabaseTriggers,Defaults,ExtendedProperties,ExternalDataSources,ExternalFileFormats,ExternalTables,Filegroups,FileTables,FullTextCatalogs,FullTextStoplists,MessageTypes,PartitionFunctions,PartitionSchemes,Permissions,Queues,RemoteServiceBindings,RoleMembership,Rules,SearchPropertyLists,SecurityPolicies,Sequences,Services,Signatures,SymmetricKeys,Synonyms,UserDefinedDataTypes,UserDefinedTableTypes,ClrUserDefinedTypes,Users,XmlSchemaCollections,Audits,Credentials,CryptographicProviders,DatabaseAuditSpecifications,DatabaseScopedCredentials,Endpoints,ErrorMessages,EventNotifications,EventSessions,LinkedServerLogins,LinkedServers,Logins,Routes,ServerAuditSpecifications,ServerRoleMembership,ServerRoles,ServerTriggers"

Upvotes: 4

Views: 2449

Answers (1)

dtc
dtc

Reputation: 185

So, I answer to myself: there were 2 things wrong in my initial attempt:

  • When you want to use multiple values in a parameter, you must separate them by semicolon (;) instead of colon.
  • If you want to exclude more than one object, the parameter name must be in plural: /p:ExcludeObjectTypes

So, with something like the following I achieved what I wanted:

/p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials

Upvotes: 9

Related Questions