sql-serverazure-devopsdacpac

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

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 enter image description here enter image description here enter image description here enter image description here enter image description here

When I am trying to finish it, I got an error enter image description here

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)?

Solution

  • 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 enter image description here

    2. Create a repo in Azure DevOps and push the changes. enter image description here

    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'
    1. Create a classic release pipeline to deploy the DACPAC file to your target DB.