发布 Visual Studio 数据库项目时可以忽略登录吗? [英] Can I ignore logins when publishing a Visual Studio Database project?

问题描述

我们有一个使用 SQL Server 身份验证的 SQL Server，用户可以部署，其他用户可以阅读，为了简单起见，我将它们称为部署"和网络".

We have a SQL Server that uses SQL Server Authentication, with users that can deploy, and others that can read, for the sake of simplicity, I'll call them "deploy" and "web".

我在使用 Visual Studio (2012) 数据库项目设置权限时遇到困难，因为部署"用户没有足够的权限来创建新的服务器登录名.

I'm having difficulty setting permissions up using a Visual Studio (2012) Database project, as the "deploy" user does not have sufficient permissions to create new server logins.

我可以添加脚本来执行以下操作:

I can add scripts to do things like:

GRANT SELECT ON foo.bar TO [web]

然后这很生气(SQL71501:权限有一个未解析的对象 [web] 引用.")，直到我添加:

This then sulks (with "SQL71501: Permission has an unresolved reference to object [web].") until I add:

CREATE USER [web] FOR LOGIN [web];

然后这很生气(SQL71501:用户:[web] 有一个未解析的登录 [web] 引用.")，直到我添加:

This then sulks (with "SQL71501: User: [web] has an unresolved reference to Login [web].") until I add:

CREATE LOGIN [web] WITH PASSWORD = '******';

然后无法发布:

删除权限...
正在删除权限...
正在创建 [webuser]...
(67,1):SQL72014:.Net SqlClient 数据提供程序:Msg 15247，级别 16，状态 1，第 1 行用户无权执行此操作.
(67,0): SQL72045: 脚本执行错误.执行的脚本:
创建登录 [网络用户]
WITH PASSWORD = '**';

执行批处理时出错.

这没有意义，因为用户已经存在，所以不需要创建

This doesn't make sense, as the user already exists, so shouldn't need creating

如何允许通过部署用户发布，而无需每次都尝试(重新)创建登录?或者，是否可以引用外部创建的用户，而不必发布它?

How can I allow publishing via the deployment user without trying to (re)create the login each time? Or, is it possible to reference the externally created user, without having to publish it?

推荐答案

由于用户已经存在，我怀疑用于执行部署的用户没有查看它的权限.以下应该是您解决此问题所需的全部内容.

As the user already exists, I suspect that the user that is used to perform the deploy doesn't have rights to view it. The below should be all you need to resolve this.

在 LOGIN::web 上授予查看定义以进行部署

这篇关于发布 Visual Studio 数据库项目时可以忽略登录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！