Deploying Database Role Memberships to Test Environments

In this article

This topic describes how to add user accounts to database roles as part of a solution deployment to a test environment.

When you deploy a solution containing a database project to a staging or production environment, you typically don't want the developer to automate the addition of user accounts to database roles. In most cases, the developer won't know which user accounts need to be added to which database roles, and these requirements could change at any time. However, when you deploy a solution containing a database project to a development or test environment, the situation is usually rather different:

The developer typically re-deploys the solution on a regular basis, often several times a day.

The database is typically re-created on every deployment, which means that database users must be created and added to roles after every deployment.

The developer typically has full control over the target development or test environment.

In this scenario, it's often beneficial to automatically create database users and assign database role memberships as part of the deployment process.

The key factor is that this operation needs to be conditional based on the target environment. If you're deploying to a staging or a production environment, you want to skip the operation. If you're deploying to a developer or test environment, you want to deploy role memberships without further intervention. This topic describes one approach you can use to address this challenge.

This topic forms part of a series of tutorials based around the enterprise deployment requirements of a fictional company named Fabrikam, Inc. This tutorial series uses a sample solution—the Contact Manager solution—to represent a web application with a realistic level of complexity, including an ASP.NET MVC 3 application, a Windows Communication Foundation (WCF) service, and a database project.

The deployment method at the heart of these tutorials is based on the split project file approach described in Understanding the Project File, in which the build process is controlled by two project files—one containing build instructions that apply to every destination environment, and one containing environment-specific build and deployment settings. At build time, the environment-specific project file is merged into the environment-agnostic project file to form a complete set of build instructions.

Executing the Script on the Target Database

Ideally, you'd run any required Transact-SQL scripts as part of a post-deployment script when you deploy your database project. However, post-deployment scripts don't allow you to execute logic conditionally based on solution configurations or build properties. The alternative is to run your SQL scripts directly from the MSBuild project file, by creating a Target element that executes a sqlcmd.exe command. You can use this command to run your script on the target database:

sqlcmd.exe –S [Database server] –d [Database name] –i [SQL script]

Note

For more information on sqlcmd command-line options, see sqlcmd Utility.

Before you embed this command in an MSBuild target, you need to consider under what conditions you want the script to run:

The target database must exist before you change its role memberships. As such, you need to run this script after the database deployment.

You need to include a condition so that the script is only executed for test environments.

If you're running a "what if" deployment—in other words, if you're generating deployment scripts but not actually running them—you shouldn't run the SQL script.

If you're using the split project file approach described in Understanding the Project File, as demonstrated by the Contact Manager sample solution, you can split the build instructions for your SQL script like this:

Any required environment-specific properties, together with the property that determines whether to deploy permissions, should go in the environment-specific project file (for example, Env-Dev.proj).

The MSBuild target itself, together with any properties that will not change between destination environments, should go in the universal project file (for example, Publish.proj).

In the environment-specific project file, you need to define the database server name, the target database name, and a Boolean property that lets the user specify whether to deploy role memberships.

In the universal project file, you need to provide the location of the sqlcmd executable and the location of the SQL script you want to run. These properties will remain the same regardless of the destination environment. You also need to create an MSBuild target to execute the sqlcmd command.

Notice that you add the location of the sqlcmd executable as a static property, as this could be useful to other targets. In contrast, you define the location of your SQL script and the syntax of the sqlcmd command as dynamic properties within the target, as they will not be required before the target is executed. In this case, the DeployTestDBPermissions target will only be executed if these conditions are met:

The DeployTestDBRoleMemberships property is set to true.

The user hasn't specified a WhatIf=true flag.

Finally, don't forget to invoke the target. In the Publish.proj file, you can do this by adding the target to the dependency list for the default FullPublish target. You need to ensure that the DeployTestDBPermissions target is not executed until the PublishDbPackages target has been executed.

Conclusion

This topic described one way in which you can add database users and role memberships as a post-deployment action when you deploy a database project. This is typically useful when you regularly re-create a database in a test environment, but it should usually be avoided when you deploy databases to staging or production environments. As such, you should ensure that you use the necessary conditional logic so that database users and role memberships are only created when it's appropriate to do so.

The feedback system for this content will be changing soon. Old comments will not be carried over. If content within a comment thread is important to you, please save a copy. For more information on the upcoming change, we invite you to read our blog post.