如何处理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.

我已经基于此

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 SERVICE \ MSSQL $ 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 SERVICE\MSSQL$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的想法，即根据包含以下名称的变量创建部署后脚本来处理权限.环境/配置.您可以在此处找到(存档的)文章:

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-perperents-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屋！