Dacpac deployment to Azure via SSMS failed: Cannot alter the role db_owner

Question

I am trying to use a dacpac file created using the Azure DevOps pipeline. I am trying to use the Upgrade data-tier Application option in SQL Server Management Studio 19.1.56.0.

This steps by step in Upgrade data-tier Application

When I am trying to finish it, I got an error

I tried to save the T-SQL script and comment out the lines and blocks that are responsible for deleting users, but it didn't work for some reason.

like:

/*
GO
PRINT N'Dropping Role Membership [db_owner] for [blala]...';


GO
EXECUTE sp_droprolemember @rolename = N'db_owner', @membername = N'blala';


GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END

IF OBJECT_ID(N'tempdb..#tmpErrors') IS NULL
    CREATE TABLE [#tmpErrors] (
        Error INT
    );

IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
*/

end etc...

Is there any way to fix this at the time of upgrade. Or dacpac file only needs to be recreated in Azure DevOps (and how)?

Answer 1

The following are the steps I use to deploy SQL server using DevOps pipeline via DACPAC file for your reference.

1. Create a SQL Server DB project (SSTD) in VS and import my DB into the project. You can make changes to the DB in the project.

   Right click on the project -> Import ->Database

2. Create a repo in Azure DevOps and push the changes.

3. Build the project to get the DACPAC file in a pipeline.

trigger:
- none

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

- task: CopyFiles@2
  inputs:
    SourceFolder: '$(system.defaultworkingdirectory)'
    Contents: '**\bin\$(BuildConfiguration)\**'
    TargetFolder: '$(build.artifactstagingdirectory)'

- task: PublishPipelineArtifact@1
  inputs:
    targetPath: '$(build.artifactstagingdirectory)'
    artifact: 'drop'
    publishLocation: 'pipeline'

4. Create a classic release pipeline to deploy the DACPAC file to your target DB.

• Select the build pipeline above as an artifact.

• Deploy using SQL Server database deploy task. (For Azure SQL, you can use Azure SQL Database deployment task. For Azure SQL Data Warehouse, you can use Azure SQL Data Warehouse deployment task.)

• Ensure the user you use to login has enough permissions to make the changes you define in your SSTD project.