3

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"

dtc
  • 155
  • 1
  • 7

1 Answers1

7

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

dtc
  • 155
  • 1
  • 7
  • Fabulous! Just what I needed Thank you! – Roberto Bonini Aug 25 '21 at 20:13
  • This is not working for me, Can you plz comment If I only wanted to delete tables from target database which is not in source tables. I tried your solution and some others also. Facing following errors *** Could not deploy package. Warning SQL72012: Warning SQL72015: Warning SQL72014: Warning SQL72045: @dtc – Abuzar Ansari Nov 17 '22 at 07:44
  • I think you can run pre-deployment script with your delete statement. https://learn.microsoft.com/en-us/sql/ssdt/how-to-specify-predeployment-or-postdeployment-scripts?view=sql-server-ver16 – dtc Dec 06 '22 at 18:10