如何在 SSDT 中将登录对象作为安全(用户)对象部署的一部分进行管理.用户未映射

Question

我正在使用Microsoft SQL Server Data Tools和Visual Studio来控制新数据库项目的架构。我希望管理此项目中的安全对象(用户、登录名)和权限。我正在使用集成安全性、域托管服务帐户作为登录名。发布项目时，正在创建 User 对象，这些对象未正确链接到 master 数据库中同名的 Login 对象。在 SSMS 中，用户对象以红叉显示。在 Windows 登录帐户下运行的应用程序无法连接。

我随后无法使用ALTER语句或sp_update_users_login将用户映射到登录名。ALTER方法完成时没有错误，sp_update_users_login返回错误：">登录 [..]不存在或无效"。

在 SSDT 项目中，我的用户安全对象设置如下：CREATE USER [DOMAINMSAAccount$] FOR LOGIN "DOMAINMSAAccount$"

老实说，我不完全理解FOR LOGIN和FROM LOGIN之间的区别。

我知道我可以从这个项目中删除安全对象并手动管理，但我在数十个表上设置了非常细粒度的限制，宁愿这是版本控制的。

Answer 1

我的解决方案是假设通过SSDT创建的用户以某种方式被破坏并完全绕过它。

我将所有权限直接移动到角色而不是用户。

然后，我创建了一个部署后脚本，用于创建用户并将其添加到角色。这工作得很好。

Answer 2

我倾向于避免数据库中的用户/登录。使用数据库角色并将用户添加到相应的角色，而不是直接授予权限。

如果您确实需要管理登录名，您可以通过创建 Login 对象并处理这些对象来做到这一点，但我发现大多数环境对不同的环境使用不同的登录名。

我用杰米·汤姆森(Jamie Thomson)的代码跑了一段时间，并在博客上写了它。这可能会给你一些想法，但会增加部署后部分的大小。 http://schottsql.com/2013/05/14/ssdt-setting-different-permissions-per-environment/

Answer 3

我曾经在SQL Server Data Tools数据库项目中避免使用用户，但多年来学会了使用它们，我最终使用分支方案在不同的环境中管理它，因为在大多数情况下，如Peter Schott所说，不同的环境有不同的用户。在我的项目中，我通常将结构设置为类似于以下内容：

DatabaseName
| - Properties
| - References
| - ChangeScripts
| - SchemaName
| | - Functions
| | - Sequences
| | - Stored Procedures
| | - Tables
| | - Views
| - PublishProfiles
| - Security
| | - CustomRole.sql
| | - Permissions.sql
| | - RoleMemberships.sql
| | - Domain_ManagedServiceAccount_.sql --(The _after the name is reference to $)
| | - Domain_ManagedServiceAccount_Login.sql

在用户文件(Domain_ManagedServiceAccount_.sql上面)中，我放置了类似于以下内容的数据库代码(可能适用于用户或组，但我正在显示托管服务帐户的语法)：

CREATE USER [DomainManagedServiceAccount$] FOR LOGIN [DomainManagedServiceAccount$];
GO
GRANT CONNECT TO [DomainManagedServiceAccount$];

GRANT CONNECT很重要，因为没有它，用户将始终失去在部署时连接的权限，并且它困扰着我，直到我花时间找出问题所在。

在某些解决方案中，我有多个数据库项目，登录名只需要在一个项目中。需要登录名的项目(通常每个解决方案一个项目)是唯一具有登录名的项目，以避免每个数据库项目中的数据库用户生成错误。服务器登录信息位于登录文件(Domain_ManagedServiceAccount_Login.sql上面)中，代码类似于以下内容：

CREATE LOGIN [DomainManagedServiceAccount$]
FROM WINDOWS WITH DEFAULT_LANGUAGE = [us_english];

我在 RoleMemberships.sql 文件中管理数据库角色成员身份。该文件类似于以下内容：

ALTER ROLE [<RoleName>] ADD MEMBER [DomainManagedServiceAccount$];
GO

通过此设置，我不再需要避免部署用户，并且可以完全管理数据库项目中的数据库用户。我也永远不需要为用户或角色成员身份使用部署前或部署后脚本。通过这样做，我还能够使用发布功能，因此我最终将发布设置保存到发布配置文件中，并将其保存在 PublishProfiles 文件夹下，部署现在就像转到发布配置文件并单击"生成脚本"或"发布"按钮一样简单。很多时候，生成解决方案或项目会公开您可能需要修复的内容，以便获得干净的代码，在项目和数据库服务器之间进行架构比较时，这些代码有时并不明显。

在某些方面，缺点是我有一堆冗余代码，因为每个环境都有一个分支，但从积极的方面来说，我在数据库项目环境分支中有一个特定环境的副本，并且可以在那里进行更改而无需访问数据库，直到我准备好。我通常只将 ChangeScripts 部分用于生产环境，以记录所有已部署的更改。使用这种分支风格的另一个缺点是有时管理分支之间的安全更改可能会很痛苦，例如将新用户添加到 dev 分支，然后将该更改合并到 QA，然后确保不只是接受来自 dev 的代码，这些代码会用开发用户覆盖 QA 中的用户。多年来，我已经学会了要查找的内容，这通常意味着如果有 database.proj 更改，请始终使用合并工具手动合并更改，以确保它以我想要的方式结束。

无论您是否决定分支，我使用它的设置都可以使特定的数据库服务器以及登录名和用户与项目保持同步，并且除非服务器上不存在用户，否则它不会重新创建用户。当将生产数据库还原到没有相同用户的环境中时，这很好，并且数据库发布将针对该环境轻松处理用户部分。