将数据库角色成员身份部署到测试环境

项目
07/13/2023

本主题介绍如何将用户帐户添加到数据库角色，作为解决方案部署到测试环境的一部分。

将包含数据库项目的解决方案部署到过渡或生产环境时，通常不希望开发人员自动将用户帐户添加到数据库角色。在大多数情况下，开发人员不知道需要将哪些用户帐户添加到哪些数据库角色，这些要求随时可能更改。但是，将包含数据库项目的解决方案部署到开发或测试环境时，情况通常会大相径庭：

开发人员通常会定期重新部署解决方案，通常每天部署几次。

数据库通常在每次部署中重新创建，这意味着必须在每次部署后创建数据库用户并将其添加到角色。

开发人员通常完全控制目标开发或测试环境。

在此方案中，在部署过程中自动创建数据库用户并分配数据库角色成员身份通常很有用。

关键因素是此操作需要基于目标环境有条件。如果要部署到过渡环境或生产环境，则需要跳过该操作。如果要部署到开发人员或测试环境，则无需进一步干预即可部署角色成员身份。本主题介绍可用于解决此挑战的一种方法。

本主题是一系列教程的一部分，这些教程基于名为 Fabrikam， Inc 的虚构公司的企业部署要求。本教程系列使用示例解决方案（ Contact Manager 解决方案）来表示具有实际复杂程度的 Web 应用程序，包括 ASP.NET MVC 3 应用程序、Windows Communication Foundation (WCF) 服务和数据库项目。

这些教程的核心部署方法基于了解项目文件中所述的拆分项目文件方法，其中生成过程由两个项目文件控制，一个项目文件包含适用于每个目标环境的生成说明，另一个包含特定于环境的生成和部署设置。在生成时，特定于环境的项目文件将合并到与环境无关的项目文件中，形成一组完整的生成指令。

任务概述

本主题假定：

使用拆分项目文件方法进行解决方案部署，如了解项目文件中所述。
从项目文件调用 VSDBCMD 以部署数据库项目，如了解生成过程中所述。

若要在将数据库项目部署到测试环境时创建数据库用户并分配角色成员身份，需要：

创建进行必要数据库更改的 Transact 结构化查询语言 (Transact-SQL) 脚本。
创建使用 sqlcmd.exe 实用工具运行 SQL 脚本的Microsoft 生成引擎 (MSBuild) 目标。
将项目文件配置为在将解决方案部署到测试环境时调用目标。

本主题将演示如何执行其中每个过程。

编写数据库角色成员身份脚本

可以通过许多不同的方式在所选的任何位置创建 Transact-SQL 脚本。最简单的方法是在 Visual Studio 2010 中的解决方案中创建脚本。

创建 SQL 脚本

在“解决方案资源管理器”窗口中，展开数据库项目节点。
右键单击“ 脚本” 文件夹，指向“ 添加”，然后单击“ 新建文件夹”。
键入 Test 作为文件夹名称，然后按 Enter。
右键单击“ 测试 ”文件夹，指向“ 添加”，然后单击“ 脚本”。
在“ 添加新项 ”对话框中，为脚本指定一个有意义的名称， (例如 AddRoleMemberships.sql) ，然后单击“ 添加”。
在 AddRoleMemberships.sql 文件中，添加以下项的 Transact-SQL 语句：
1. 为将访问数据库的SQL Server登录名创建数据库用户。
2. 将数据库用户添加到任何所需的数据库角色。

该文件应如下所示：

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

保存文件。

在目标数据库上执行脚本

理想情况下，在部署数据库项目时，将任何必需的 Transact-SQL 脚本作为部署后脚本的一部分运行。但是，部署后脚本不允许根据解决方案配置或生成属性有条件地执行逻辑。另一种方法是通过创建执行 sqlcmd.exe 命令 的 Target 元素，直接从 MSBuild 项目文件运行 SQL 脚本。可以使用此命令在目标数据库上运行脚本：

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

注意

有关 sqlcmd 命令行选项的详细信息，请参阅 sqlcmd 实用工具。

在 MSBuild 目标中嵌入此命令之前，需要考虑要在什么条件下运行脚本：

在更改其角色成员身份之前，目标数据库必须存在。因此，需要在数据库部署后运行此脚本。
需要包含条件，以便脚本仅针对测试环境执行。
如果运行的是“what if”部署（换句话说，如果要生成部署脚本，但实际上并未运行它们），则不应运行 SQL 脚本。

如果使用了解项目文件中所述的拆分项目文件方法（如 Contact Manager 示例解决方案所示），则可以拆分 SQL 脚本的生成说明，如下所示：

任何必需的特定于环境的属性以及确定是否部署权限的属性，都应进入特定于环境的项目文件 (例如 Env-Dev.proj) 。
MSBuild 目标本身以及不会在目标环境之间更改的任何属性应位于通用项目文件 (例如 Publish.proj) 。

在特定于环境的项目文件中，需要定义数据库服务器名称、目标数据库名称和布尔值属性，以便用户指定是否部署角色成员身份。

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

在通用项目文件中，需要提供 sqlcmd 可执行文件的位置以及要运行的 SQL 脚本的位置。无论目标环境如何，这些属性都将保持不变。还需要创建 MSBuild 目标以执行 sqlcmd 命令。

<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>

请注意，将 sqlcmd 可执行文件的位置添加为静态属性，因为这可能会对其他目标有用。相比之下，将 SQL 脚本的位置和 sqlcmd 命令的语法定义为目标中的动态属性，因为在执行目标之前不需要它们。在这种情况下，仅当满足以下条件时，才会执行 DeployTestDBPermissions 目标：

DeployTestDBRoleMemberships 属性设置为 true。
用户尚未指定 WhatIf=true 标志。

最后，不要忘记调用目标。在 Publish.proj 文件中，可以通过将目标添加到默认 FullPublish 目标的依赖项列表来执行此操作。你需要确保在执行 PublishDbPackages 目标之前不会执行 DeployTestDBPermissions 目标。

<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>

结论

本主题介绍了在部署数据库项目时添加数据库用户和角色成员身份作为部署后操作的一种方法。在测试环境中定期重新创建数据库时，这通常很有用，但在将数据库部署到过渡或生产环境时，通常应避免这种情况。因此，应确保使用必要的条件逻辑，以便仅在适合时创建数据库用户和角色成员身份。

深入阅读

有关使用 VSDBCMD 部署数据库项目的详细信息，请参阅部署数据库项目。有关为不同目标环境自定义数据库部署的指南，请参阅为多个环境自定义数据库部署。有关使用自定义 MSBuild 项目文件来控制部署过程的详细信息，请参阅了解项目文件和了解生成过程。有关 sqlcmd 命令行选项的详细信息，请参阅 sqlcmd 实用工具。

通过

将数据库角色成员身份部署到测试环境

任务概述

编写数据库角色成员身份脚本

在目标数据库上执行脚本

结论

深入阅读

反馈

其他资源