Using Publish Profiles to Deploy a DACPAC Database Without User Accounts

Often, when developing a database as a data-tier application package (more commonly known as DACPAC), you will need to execute regular deployments onto a local SQL Server instance, to verify that you haven’t accidentally broken anything when changing your database. Where this can get potentially complicated is when you are also managing security objects (contained user accounts, database roles, etc.) within the DACPAC. Consider the following scenarios:

You are deploying your database to an Azure SQL database. You wish to use Azure Active Directory (AAD) authentication into your database, with user accounts mapped to user principal name (UPN’s) on the AAD tenant. Attempting to deploy a database with these defined to an on-premise SQL Server database will cause errors, as this feature is only available on Azure SQL databases.
You are developing a database that will reside within a different Active Directory (AD) domain than the one your user account currently exists in. Any attempt to create users bound to the other AD tenant will cause the deployment to fail, for pretty much the same reasons as the scenario outlined earlier.

In both situations, finding a mechanism to allow you to skip the creation of these objects becomes highly desirable. By doing so, it becomes possible to deploy locally much faster. Then, as part of more centralised build cycles or formal pull requests, deployments can then target infrastructure that has been configured with the correct AD / AAD associations and, therefore, will allow the creation of any object without issue. Of course, there is an argument to be made to completely exclude user account management from within the structure of your DACPAC…but that’s a topic for a whole blog post itself ;)

The solution to this problem lies in defining an appropriate publish profile for your DACPAC, which then instructs your chosen deployment tool - SQLPackage.exe, Visual Studio, or Azure DevOps - on how to carry out the deployment. For example, you can force the deployment to error if any potential data loss is detected, remove any objects that exist in target, but not in source, or even specifically exclude objects entirely from being deployed out. The profile is defined as an XML file. It is incredibly useful to use if you plan to automate your SQL database deployments using Azure Pipelines, as you can then re-use this profile or even modify it at runtime. Out of all the available options listed in the earlier example, it is the last set that most interest us in this situation, as this is what allows us to make use of the following properties to disable the deployment of any security-related object to our database:

ExcludeUsers
ExcludeLogins
ExcludeDatabaseRoles

By setting these options to True within our publish profile, creation or modification of these objects will be skipped entirely during any database deployment. An example publish.xml structure, with these settings enabled, can be viewed below:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MyDatabase</TargetDatabaseName>
    <DeployScriptFileName>SQL.MyDatabase.sql</DeployScriptFileName>
    <ExcludeUsers>True</ExcludeUsers>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ExcludeLogins>True</ExcludeLogins>
    <ExcludeDatabaseRoles>True</ExcludeDatabaseRoles>
  </PropertyGroup>
</Project>

Simply reference this as part of your chosen DACPAC deployment tool, and everything should work as expected 🙂

Keeping developers productive is an important concern if you want to ensure they deliver regular business value as part of their daily work. While you can make the argument that the inclusion of user account objects within a database causes more hassle than help, it may still be desirable to ensure these are captured in source and are - therefore - documented as part of the structure of your database. To ensure that you can meet this objective, the use of a publish profile can meet the requirement rather nicely. They not only let developers tailor how to perform their local deployments, so that any blocking elements are excluded entirely, but also become useful in defining an “agreed” template for how to deploy out a database, depending on the environment being targeted. Enforcing this level of conformance may sound disheartening, but if it ultimately results in more successful builds and regular releases, then that can only be a good thing.