在 SQL Server 2005 中更改上下文（模拟）和使用服务器链接时出现问题
当我在 SQL Server 2005 中将模拟与数据库链接结合起来时，我得到了一个奇怪的行为。首先，我使用简单的 SQL Server 身份验证和登录名 'John' 连接到数据库服务器。在此服务器上，定义了服务器链接remote_sqlserver。我已经在此服务器中拥有 mydb 的 SELECT 权限。当我简单地查询此服务器链接上的数据库上的表时：
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
之后，我尝试使用相同的登录名进行模拟（不要问为什么要这样做，我只是在试验；））
EXECUTE AS LOGIN = 'John'
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Error: "Login failed for user: 'John'"
当我恢复时，它再次起作用：
REVERT
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
你知道为什么我在模拟时遇到错误，尽管相同的登录名可以在不模拟的情况下查询表吗？
顺便说一句：在“模拟自己”之后，如果我查询本地数据库（当然，我有足够的权限），我不会收到任何错误。仅当我通过服务器链接查询远程数据库时才会发生这种情况。
I get a strange behaviour when I combine impersonation with database links in SQL Server 2005. First, I connect to a database server using simple SQL Server Authentication with Login 'John'. On this server, a server link remote_sqlserver is defined. I already have SELECT privileges for mydb in this server. When I simply query a table on a DB on this server link:
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
After that, I try impersonation with the same Login (don't ask why would one do that, I'm just experimenting ;) )
EXECUTE AS LOGIN = 'John'
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Error: "Login failed for user: 'John'"
When I revert, it works again:
REVERT
SELECT count(*)
FROM remote_sqlserver.mydb.dbo.mytable -- Works!
Do you have any idea, why I get an error with impersonation, although the same Login can query the table without impersonation?
BTW: After "impersonation as self", if I query a local database, (of course, for which I have enough privileges) I don't get any error. It only happens when I query a remote DB via server link.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您应该阅读联机丛书文章“使用 EXECUTE AS 扩展数据库模拟”。
当您使用 EXECUTE AS 访问远程服务器时，必须将远程服务器配置为信任调用者。即使“父”和“子”登录名相同（即“John”），因为您使用的是 EXECUTE AS，但必须建立信任关系。即使登录相同，身份验证“路径”也不同。
You should read the Books Online article "Extending Database Impersonation by Using EXECUTE AS".
When you use EXECUTE AS to access a remote server, the remote server has to be configured to trust the caller. Even though the the "parent" and "child" logins are the same (i.e. "John") since you are using EXECUTE AS the trust relationships have to be set up. The authentication "path" is different even though the login is the same.
如果身份验证可以通过简单的方式来信任您所说的自己，那不是很有趣吗？有人会说“我是约翰，把我账户里的所有钱都给我”，银行就会支付现金。现在，幸运的是，各地使用的身份验证系统的要求会更高一些，当一个人出现并说“我是约翰”时，他会被问到“你好，约翰，那么……你的密码是什么？”。
这里也发生了完全相同的事情。您可能会注意到，当您说
EXECUTE AS Login = 'John'时，您没有提供密码。因此，SQL Server 实例可能被“愚弄”，认为你是“John”，但 SQL 之外没有人会相信你（SQL 内部的“愚弄”是一个关于信任和特权的长篇故事，事实上，真正发生的事情是更多）例如“我是 SYSADMIN，我说你应该相信这个用户是 John！”如果你想访问 SQL Server 系统之外的任何内容并且成为 John，那么你需要指定 John 的密码。通常的方法是使用凭据对象，
CREATE CREDENTIAL。Wouldn't it be interesting if authentication would work by simple means of trusting who you say you are? One would say "I'm John, give me all the money in my account" and the bank would pay up the cash. Now, fortunately, the authentication systems used everywhere will be just a tad more demanding and when one shows up and say "I'm John", he will be challenged "Hullo John, so... what is your password?".
Exactly the same thing goes on here. You may notice that when you say
EXECUTE AS Login = 'John'you did not provide a password. So the SQL Server instance may have been 'fooled' that you're "John", but nobody outside SQL will believe you (and the "fooling" inside SQL is a long story of trust and privileges in fact, what really happens is more like "I am the SYSADMIN and I SAY that thou shall believe this user is John!".If you want to access anything outside the SQL Server system and be John, then you need to specify John's password. The usual way is by using a credential object, with
CREATE CREDENTIAL.如果您还没有，那么快速阅读一下 EXECUTE 可能是值得的AS 和所有权链，以防他们可以摆脱任何阐明你遇到的问题
If you haven't already, it might be worth having a quick read up on EXECUTE AS and Ownership Chains in case they can shed any light on the problems you're having