sql-serverpowershelldacpacsqlpackage

How can I exclude a Login's default database setting in DACPAC Publish


I am writing a Powershell script to create a SQL Server dacpac file from our QA database and publish it to the local db. I am using SQLPackage.exe. When I publish, I am getting an error:

Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15010, Level 16, State 1, Line 1 The database 'ESIncome' does not exist. Supply a valid database name. To see available databases, use sys.databases.
Error SQL72045: Script execution error.  The executed script:
CREATE LOGIN [Domain\sg.AllApplicationDevelopers]
    FROM WINDOWS WITH DEFAULT_DATABASE = [ESIncome], DEFAULT_LANGUAGE = [us_english];

This error happens because the loginDomain\sg.AllApplicationDevelopers has a default db set to ESIncome and that db doesn't exist in our local server (and I can't import it). Is there a way to not set the user's default db or to ignore this error?

I tried using the Publish option "/p:ExcludeObjectTypes=Logins;ServerRoleMembership;ServerRoles;Credentials;Rolemembership;DatabaseRoles;Users" but that causes other problems. (I'm not inclined to describe those problems because that would take us down a different rabbit hole and confuse the situation.) Suffice it to say its simpler if the Users are included.

I created a sql script using Action:/Script. It sets the same default db.

Here is the PS script

$dacpacFile = "eServices.dacpac"

$sourceServer = "STG-SQL01"
$sourceDatabase = "eServices"

$targetServer = "localhost\SQLEXPRESS"
$targetDatabase = "eServices"

$sqlpackage = "C:\Users\<username>\.dotnet\tools\sqlpackage.exe"

Write-Host "Generating Dacpac file..." -ForegroundColor Green
$sourceConnectionString = "Server=$sourceServer;Database=$sourceDatabase;Encrypt=False; Integrated Security=SSPI;"
$arguments = "/Action:""Extract""", "/SourceConnectionString:""$sourceConnectionString""", "/TargetFile:""./$dacpacFile"""
$arguments
& $sqlpackage $arguments

if($(Write-Host "Publish to '$targetServer'? Y / N:  " -NoNewline -ForegroundColor Green; Read-Host) -eq 'y')
{
    Write-Host "Applying file to target db..." -ForegroundColor Green
    $targetConnectionString = "Server=$targetServer;Database=$targetDatabase;Encrypt=False; Integrated Security=SSPI;"
    $arguments = "/Action:Publish", "/SourceFile:""./$dacpacFile""",  "/TargetConnectionString:""$targetConnectionString""", "/p:IncludeCompositeObjects=True"
    & $sqlpackage $arguments
}


Solution

  • For ExcludeObjectTypes you give list of values separated by semicolon which is a command separator for CMD and Powershell so the first ; breaks the command. Also /p parameters should not be quoted.

    Try

    /p:ExcludeObjectTypes='Logins;Users;etc'

    or split it into multiple params

    /p:ExcludeObjectType=Logins /p:ExcludeObjectType=Users

    we use multiple ExcludeObjectType params approach.

    Also I'd suggest to use single quote ' instead of " for other parameter values

    like this:

    "/SourceFile:'./$dacpacFile' /TargetConnectionString='$connectionString'"

    to avoid possible ambiguity. Also to avoid ambiguity and improve readability I'd suggest to define params as an explicit array and convert it into string explicitly

    $arguments = @(
      '/Action:Extract', 
      "/SourceConnectionString`:'$sourceConnectionString'",
      "/TargetFile`:'./$dacpacFile'"
    ) -join ' '
    

    note that : can be treated by Powershell string processor as a scope specifier so it should be escaped in double-quoted strings.

    Also you can use /Profile parameter which expects publish.xml file. Many options can be defined in such publish.xml file and the argument list for sqlpackage.exe can be reduced significantly. Most of connection string parameters and exclusions can be defined in there. For specific connection you'd need to provide /TargetServerName and /TargetDatabaseName only in CLI call.

    For troubleshooting your script you can add additional parameter

    /DiagnosticsLevel:Verbose