PowerShell script to remove AutoDrop and Grant.KillDatabaseConnection.Database from Bacpac model file

Fix-BacpacModel.ps1

# This script can be used to 
# - remove AutoDrop properties
# - remove Grant.KillDatabaseConnection.Database elements of type SqlPermissionStatement
# from the model file of a SQL Server 2022 (or equivalent Azure SQL) bacpac backup.
# This enables restoring the bacpac on a SQL server 2019.
# The d365fo.tools module needs to be installed to run this script.
# See also 
# - https://github.com/d365collaborative/d365fo.tools/issues/747 (for AutoDrop)
# - https://github.com/d365collaborative/d365fo.tools/issues/820 (for Grant.KillDatabaseConnection.Database)
# Original script by @batetech in https://www.yammer.com/dynamicsaxfeedbackprograms/#/Threads/show?threadId=2382104258371584
# Grant.KillDatabaseConnection.Database changes by @batetech in https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2782521902366720
# Minor changes by @FH-Inway
# Gist of script: https://gist.github.com/FH-Inway/f485c720b43b72bffaca5fb6c094707e

# Add your file information here
$bacpacFileNameAndPath = "C:\Temp\MyBackup.bacpac"
# Will be created by script. Existing files will be overwritten.
$modelFilePath = "C:\Temp\BacpacModel.xml" 
$modelFileUpdatedPath = "C:\Temp\UpdatedBacpacModel.xml"

function Local-FixBacPacModelFile
{
    param(
        [string]$sourceFile, 

        [string]$destinationFile,

        [int]$flushCnt = 500000
    )

    if($sourceFile.Equals($destinationFile, [System.StringComparison]::CurrentCultureIgnoreCase))
    {
        throw "Source and destination files must not be the same."
        return;
    }

    $searchForString = '<Property Name="AutoDrop" Value="True" />';
    $replaceWithString = '';
    
    $killDBConnStart = '<Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].';
    $killDBConnEnd = '</Element>';

    #using performance suggestions from here: https://learn.microsoft.com/en-us/powershell/scripting/dev-cross-plat/performance/script-authoring-considerations
    # * use List<String> instead of PS Array @()
    # * use StreamReader instead of Get-Content
    $buffer = [System.Collections.Generic.List[string]]::new($flushCnt) #much faster than PS array using +=
    $buffCnt = 0;

    #delete dest file if it already exists.
    if(Test-Path -LiteralPath $destinationFile)
    {
        Remove-Item -LiteralPath $destinationFile -Force;
    }

    try
    {
        $stream = [System.IO.StreamReader]::new($sourceFile)
        $streamEncoding = $stream.CurrentEncoding;
        Write-Verbose "StreamReader.CurrentEncoding: $($streamEncoding.BodyName) $($streamEncoding.CodePage)"

        while ($stream.Peek() -ge 0)
        {
            $line = $stream.ReadLine()
            if(-not [string]::IsNullOrEmpty($line))
            {
                $lineIndex = $line.IndexOf($killDBConnStart, [System.StringComparison]::CurrentCultureIgnoreCase)
                if($lineIndex -ge 0)
                {
                    Write-Host "Skipping line: $line"
                    #Note: This fix assumes that the element we need to remove will be on it's own line, which it is currently. If this changes in the future and other content 
                    #      is included on the same file line, then this fix could corrupt the bacpac and we would need the fix our logic.
                    #Fix for error: Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. 
                    #     Alternatively, use the server level 'ALTER ANY CONNECTION' permission. Error SQL72045: Script execution error.  The executed script: GRANT KILL DATABASE CONNECTION TO [ms_db_configreader];  
                    #The XML block that causes this issue (so we need to remove) looks like this: 
                    <#
                    <Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configreader].[dbo]">
	                    <Property Name="Permission" Value="1114" />
	                    <Relationship Name="Grantee">
		                    <Entry>
			                    <References Name="[ms_db_configreader]" />
		                    </Entry>
	                    </Relationship>
	                    <Relationship Name="SecuredObject">
		                    <Entry>
			                    <References Disambiguator="1" />
		                    </Entry>
	                    </Relationship>
                    </Element>
                    #>
                    
                    #Loop until we get to the end tag. 
                    $foundEndTag = $false;
                    while($foundEndTag -eq $false -and $stream.Peek() -ge 0)
                    {
                        $line = $stream.ReadLine();
                        if($line.IndexOf($killDBConnEnd, [System.StringComparison]::CurrentCultureIgnoreCase) -ge 0)
                        {
                            #we found the end tag, so skip it and move on.
                            $foundEndTag = $true;
                        }     
                    }
                }
                else
                {
                    #AutoDrop fix
                    $buffer.Add($line.Replace($searchForString,$replaceWithString));
                }
            }
            else
            {
                $buffer.Add($line);
            }

            $buffCnt++;
            if($buffCnt -ge $flushCnt)
            {
                Write-Verbose "$(Get-Date -Format 'u') Flush buffer"
                $buffer | Add-Content -LiteralPath $destinationFile -Encoding UTF8
                $buffer = [System.Collections.Generic.List[string]]::new($flushCnt);
                $buffCnt = 0;
                Write-Verbose "$(Get-Date -Format 'u') Flush complete"
            }
        }
    }
    finally
    {
        $stream.Dispose()
        Write-Verbose 'Stream disposed'
    }

    #flush anything still remaining in the buffer
    if($buffCnt -gt 0)
    {
        $buffer | Add-Content -LiteralPath $destinationFile -Encoding UTF8
        $buffer = $null;
        $buffCnt = 0;
    }

}

Export-D365BacpacModelFile -Path $bacpacFileNameAndPath -OutputPath $modelFilePath -Force -Verbose

Write-Host "$(Get-Date -Format 'u') Fixing model file..."
$VerbosePreferenceOriginal = $VerbosePreference
$VerbosePreference = 'Continue' # Comment or remove this line to remove the "Flush buffer/complete" messages
Local-FixBacPacModelFile -sourceFile $modelFilePath -destinationFile $modelFileUpdatedPath -Verbose
$VerbosePreference = $VerbosePreferenceOriginal
Write-Host "$(Get-Date -Format 'u') Model file fixed."

Import-D365Bacpac -ImportModeTier1 -BacpacFile "$bacpacFileNameAndPath" -ModelFile $modelFileUpdatedPath -Verbose

# Where Local-FixBacPacModelFile is the function defined above using StreamReader with buffering instead on Get-Content piped directly to Add-Content, which on a dev VM (B8ms, HHDs) made a big difference.

Looks great! Thank you very much.