Deploying Database Role Memberships to Test Environments

Articol
07/01/2022

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.

Task Overview

This topic assumes that:

You use the split project file approach to solution deployment, as described in Understanding the Project File.
You call VSDBCMD from the project file to deploy your database project, as described in Understanding the Build Process.

To create database users and assign role memberships when you deploy a database project to a test environment, you'll need to:

Create a Transact Structured Query Language (Transact-SQL) script that makes the necessary database changes.
Create a Microsoft Build Engine (MSBuild) target that uses the sqlcmd.exe utility to run the SQL script.
Configure your project files to invoke the target when you're deploying your solution to a test environment.

This topic will show you how to perform each of these procedures.

Scripting the Database Role Memberships

You can create a Transact-SQL script in a lot of different ways, and in any location you choose. The easiest approach is to create the script within your solution in Visual Studio 2010.

To create a SQL script

In the Solution Explorer window, expand your database project node.
Right-click the Scripts folder, point to Add, and then click New Folder.
Type Test as the folder name, and then press Enter.
Right-click the Test folder, point to Add, and then click Script.
In the Add New Item dialog box, give your script a meaningful name (for example, AddRoleMemberships.sql), and then click Add.
In the AddRoleMemberships.sql file, add Transact-SQL statements that:
1. Create a database user for the SQL Server login that will access your database.
2. Add the database user to any required database roles.

The file should resemble this:

USE $(DatabaseName)
GO
CREATE USER [FABRIKAM\TESTWEB1$] FOR LOGIN[FABRIKAM\TESTWEB1$]
GO
USE [ContactManager]
GO
EXEC sp_addrolemember N'db_datareader', N'FABRIKAM\TESTWEB1$'
GO
USE [ContactManager]
GO
EXEC sp_addrolemember N'db_datawriter', N'FABRIKAM\TESTWEB1$'
GO

Save the file.

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.

<PropertyGroup>
   <CmTargetDatabase Condition=" '$(CmTargetDatabase)'=='' ">
      ContactManager
   </CmTargetDatabase>
   <DatabaseServer Condition=" '$(DatabaseServer)'=='' ">
      TESTDB1
   </DatabaseServer>
   <DeployTestDBRoleMemberships Condition="'$(DeployTestDBRoleMemberships)'==''">
      true
   </DeployTestDBRoleMemberships>
</PropertyGroup>

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.

<PropertyGroup>
   <SqlCmdExe Condition=" '$(SqlCmdExe)'=='' ">
      C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe
   </SqlCmdExe>
</PropertyGroup>

<Target Name="DeployTestDBPermissions" 
        Condition=" '$(DeployTestDBRoleMemberships)'=='true' AND 
                    '$(Whatif)'!='true' ">
   <PropertyGroup>
     <SqlScript>
        $(SourceRoot)ContactManager.Database\Scripts\Test\AddRoleMemberships.sql
     </SqlScript>
     <_Cmd>"$(SqlCmdExe)" -S "$(DatabaseServer)" 
                          -d "$(CmTargetDatabase)" 
                          -i "$(SqlScript)"
     </_Cmd>
   </PropertyGroup>
   <Exec Command="$(_Cmd)" ContinueOnError="false" />
</Target>

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.

<Project ToolsVersion="4.0" 
         DefaultTargets="FullPublish" 
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
   ...
   <PropertyGroup>
      <FullPublishDependsOn>
         Clean;
         BuildProjects;
         GatherPackagesForPublishing;
         PublishDbPackages;
         DeployTestDBPermissions;
         PublishWebPackages;
      </FullPublishDependsOn>
   </PropertyGroup>
   <Target Name="FullPublish" DependsOnTargets="$(FullPublishDependsOn)" />
</Project>

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.

Partajați prin

Deploying Database Role Memberships to Test Environments

Task Overview

Scripting the Database Role Memberships

Executing the Script on the Target Database

Conclusion

Further Reading

Feedback

Resurse suplimentare