部署错误:SomeUser不是有效的SQL Server登录名 [英] Deployment Error: SomeUser is not a valid SQL Server login

问题描述

我正在尝试为我的项目创建测试部署，但似乎无法部署它.我创建了一个具有与生产数据库相同的用户和权限的测试数据库，并在发布向导中更改了连接字符串.但是，当我尝试 部署时出现以下异常:

I'm trying to create a test deployment for my project and I can't seem to deploy it. I created a test database with the same users and permissions as the production database and changed the connection strings in the publish wizard. However, when I try to deploy I get the following exception:

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\LightSwitch\v4.0\Publish\v2.2\Microsoft.LightSwitch.Publish.targets(1572,9): error MSB4018: The "CreateSqlUser" task failed unexpectedly.
System.InvalidOperationException: 'TestApp_User is not a valid SQL Server login.  It must exist prior to deploying the application.
   at Microsoft.LightSwitch.Publish.Tasks.Deployment.CreateSqlUser.ExecuteCore()
   at Microsoft.LightSwitch.Publish.Tasks.Common.BuildTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__20.MoveNext()
Done building project "TestApp.lsxproj" -- FAILED.

TestApp_User 确实存在，并且它与我可以成功部署到的生产数据库中的用户具有相同的权限.我什至进入了发布向导中的数据连接页面，并打开了连接 对话框并测试了连接，连接成功.这两个数据库具有完全相同的用户和权限.我什至复制了数据库并备份/还原到新数据库，结果仍然相同.

TestApp_User does exist in the target database and it has the same permissions as the user in the production database that I can successfully deploy to. I even went to the data connections page in the publish wizard and opened up the connection dialog and tested the connection and it succeeded. Both databases have the exact same users and permissions. I even copied the database and did a backup/restore to a new database and still the same result.

这是怎么回事?

推荐答案

首先，我将为您提供一些背景信息.您可能已经知道所有这些，但对其他人也可能有用.

First I'll provide a little background of what's going on here.  You may already know all this but it may be useful to others as well.

这里有两个连接字符串:用于连接到SQL Server以便部署数据库的架构连接字符串，以及正在运行的应用程序用于连接到数据库的用户连接字符串. ;模式 连接字符串通常配置为使用比用户连接字符串具有更大权限的凭据，因为它需要具有创建表和其他数据库对象的能力，而这是普通应用程序用户连接的权限 不需要.

There are two connection strings here: the schema connection string, which is used to connect to SQL Server in order to deploy the database, and the user connection string, which is used by the running application to connect to the database.  The schema connection string is typically configured to use credentials that have greater permissions than the user connection string because it needs to have the ability to create tables and other DB objects which is a permission a normal application user connection doesn't require.

LightSwitch部署数据库时，还会将用户连接字符串中指定的登录名添加为该数据库的用户，因此您不必这样做.这似乎在这里不起作用. LightSwitch查询主数据库以 查看连接字符串中提供的登录名是否存在.如果不是，它将引发您所看到的异常. LightSwitch用于此检查的查询是这样的:

When LightSwitch deploys the database, it also adds the login specified in the user connection string as a user of that database so that you don't have to.  That's what appears to be not working here.  LightSwitch queries the master database to see whether the login provided in the connection string exists.  If it doesn't, it throws the exception you are seeing.  The query that LightSwitch is using for this check is this:

SELECT * FROM sys.sql_logins WHERE name = @username

@username是一个参数，其中包含用户连接字符串中指定的用户名值.该命令是使用模式连接字符串和"master"来执行的.替换为初始目录.

Where @username is a parameter containing the value of the username specified in the user connection string.  That command is executed using the schema connection string, with "master" replaced as the Initial Catalog.

如果可以的话，请尝试自己执行该命令，然后看看会得到什么结果.

Try executing that command yourself if you can and see what result you get.

这篇关于部署错误:SomeUser不是有效的SQL Server登录名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！