Deploying Databases with Object-Level Permissions

Cross-posted from Jason Lee's Blog

As I mentioned in my last post, we’re currently creating some guidance on deploying enterprise-scale applications. As we go along, I plan to blog about a few of the things that I find particularly tricky to figure out.

This time I want to look at database deployment. When you build a web application project in Visual Studio 2010, the Web Publishing Pipeline features allow you to hook into the IIS Web Deployment Tool (commonly known as “Web Deploy”) to package and optionally deploy your web application. As part of this process you can also deploy local databases to a target server environment. This is all nice and easy to configure through the project property pages in Visual Studio 2010, as shown below.

I don’t want to describe this process in any detail, you can find that elsewhere on the web (for example here).

Deploying databases in this way has advantages and disadvantages. On the plus side:

• It’s easy.
• It’s UI-driven.
• It figures out most of the settings for you.

On the downside:

• There’s no support for differential updates. In other words, the destination database is destroyed and recreated every time you deploy, so you’ll lose any data.
• Some of the default database deployment settings are unsuitable for many real-world scenarios – this is the issue I want to focus on here.

In many cases, you’ll want to avoid the Web Deploy approach altogether and use VSDBCMD.exe to deploy and update your databases, but that’s a conversation for another day. In this post I want to focus on how you can change some of the default behaviours for database deployment using Web Deploy. In particular, Web Deploy omits object-level permissions by default. This causes problems if your database (a) contains stored procedures and (b) grants execute permissions on the stored procedures to database roles.

Suppose you’re using the Visual Studio 2010/Web Deploy approach to deploy an ASP.NET membership database from a local development machine to a destination server environment. (NB you’d typically only deploy a membership database if you’ve modified the schema, otherwise it’s easier just to run ASPNET_REGSQL.exe and create the database from scratch on the destination server). You opt for a full deployment, including schema and data from the source database. On the source database, you can see that various database roles are granted execute permissions on stored procedures:

However, when the database is recreated on the destination database server, these permissions are missing:

This can be mystifying at first—essentially, you add users to the built-in database roles such as aspnet_Membership_BasicAccess and aspnet_Membership_FullAccess, but membership of these roles has no effect. They’re basically just empty roles that aren’t mapped to any permissions.

The problem is that stored procedures are “objects” in database terms (don’t ask me, I’m not a DBA), and by default Web Deploy does not include object-level permissions when it scripts the database. To change this behaviour, you need to modify the project file for your web application project.

1. In the Solution Explorer window, right-click your web application project node, and then click Unload Project.

2. Right-click the project node again, and click Edit [project file].

3. Locate the PropertyGroup element that corresponds to your build configuration (for example Release|AnyCPU).

<PropertyGroup

    Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">

3. Within this element, locate the PreSource element and add a Permissions=”True” attribute, as shown below. The Permissions attribute indicates that the database script should include all permissions, including object-level permissions, which are defined in the source database.

<PublishDatabaseSettings>

  <Objects>

    <ObjectGroup Name="ApplicationServices-Deployment" Order="1">

      <Destination Path="[Destination Database Connection String]" />

      <Object Type="dbFullSql">

        <PreSource Path="[Source Database Connection String]"

                   ScriptSchema="True"

                   ScriptData="True"

                   Permissions="True"

                   CopyAllFullTextCatalogs="False"

                   DriDefaults="True" />

        <Source Path="[Where to save a copy of the script]"
                Transacted="True" />

      </Object>

    </ObjectGroup>

  </Objects>

</PublishDatabaseSettings>

4. Save and close the project file.

There’s a whole host of settings you can add to the PreSource element to configure how your database is deployed. For example, if you want to deploy a database that already exists on the destination server, you need to add a ScriptDropsFirst=”True” attribute to the PreSource element – otherwise Web Deploy will complain that you’re trying to create objects that already exist. The full list of properties that you can set as PreSource attibutes can be tricky to track down unless you know how the database deployment process works:

• Web Deploy uses the dbFullSql provider to deploy databases (the link includes some properties you can use as PreSource attributes).
• Under the covers, the dbFullSql provider uses SQL Server Management Objects (SMO) to generate database scripts. The ScriptingOptions Properties page describes some SMO properties you can specify as PreSource attributes.

Alternatively, to get a full list of properties, you can run the following Web Deploy command:

msdeploy.exe –verb:sync –source:dbFullSql /?

There’s much more to database deployment than I can cover in a quick blog post, and we’ll cover these kinds of issues in much more detail when we publish to MSDN. For now though I hope this helps to shed some light on the intricacies of database deployment.