如何处理 Visual Studio 数据库项目中的用户和登录? [英] How to handle users and logins in Visual Studio Database Project?

问题描述

我在 SQL Server 2008 R2 中构建了一个数据库，并正在使用 Visual Studio 2010 Ultimate 为其创建一个数据库项目.

I've built a database in SQL Server 2008 R2 and am using Visual Studio 2010 Ultimate to create a database project for it.

我已经创建了一个 SQL Server 项目和数据库项目来代表我基于这个 MSDN 演练.两个项目的架构比较按预期工作，我能够将所有更改服务器复制到项目.

I've created both a SQL Server project and Database project to represent my environment based on this MSDN walkthrough. The schema comparisons for both projects work as expected and I'm able to replicate all changes server to project.

不过好像导入了一些环境特定的配置，比如登录名、用户/登录名映射、本地服务账户(例如NT SERVICEMSSQL$SQLEXPRESS2008)等等.这似乎不太理想，因为我的印象是这个数据库项目可以部署到另一台服务器上，这些环境特定的细节可能会有所不同.例如，Dev、QA 和 Prod 之间的命名实例可能不同.用户可以映射到不同的登录名.

However, it seems to have imported some environment specific configuration, such as logins, user/login mapping, local service accounts (e.g. NT SERVICEMSSQL$SQLEXPRESS2008), etc. This seems not ideal because my impression was this database project could be deployed onto another server and those environment specific details could be different. For example the named instances could be different between Dev, QA, and Prod. Users could be mapped to different logins.

我这样做是正确的还是应该采取额外的步骤来使数据库项目可移植到所有环境?

Am I doing this properly or should I be taking extra steps to make the database project portable to all environments?

我的目标是利用这个项目来

My goal is to utilize this project to

1. 在版本控制中捕获架构
2. 部署架构更改，例如通过生成更改脚本
3. 与单元测试相关，以动态创建一次性测试环境.

推荐答案

我们之前不得不处理这个问题，最终使用了 Jamie Thompson 的想法，即创建部署后脚本来处理基于包含名称的变量的权限环境/配置.您可以在此处找到(存档的)文章:https://web.archive.org/web/20190222004817/http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx

We've had to mess with this before and ended up using Jamie Thompson's idea of creating post-deployment scripts to handle permissions based on a variable containing the name of the environment/configuration. You can find the (archived) article here: https://web.archive.org/web/20190222004817/http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx

注意:Jamie 的链接显然已失效.我在这里根据它写了一些东西:http://schottsql.com/2013/05/14/ssdt-setting-different-permissions-per-environment/

Note: Jamie's link is apparently dead. I wrote up something based on it here: http://schottsql.com/2013/05/14/ssdt-setting-different-permissions-per-environment/

我还编写了一个脚本来处理权限脚本:

I also wrote a script to handle scripting of permissions:

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

我将权限设置到部署后脚本和文件夹中，这些脚本和文件夹将根据环境重新创建用户/角色/权限.我们从主要的部署后部分调用包装器"脚本，该脚本会遍历其余部分以确定要运行的部分.

Together, I set up the permissions into post-deploy scripts and folders that will recreate users/roles/permissions based on the environment. We call a "wrapper" script from the main post-deploy section that goes through the rest to figure out which section to run.

这篇关于如何处理 Visual Studio 数据库项目中的用户和登录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！