sql视图从多个数据库中选择，代理用户无权访问远程数据库
我在 sql server 数据库中设置了多个视图。这些视图是从存储过程中选择的，该存储过程的声明中具有WITH EXECUTE AS“proxyuser”子句。它执行的 select 语句是动态创建的，并使用 sp_executesql 存储过程执行。一切都工作正常，直到有必要在其中一个视图的定义中包含到不同数据库中的表的联接，以便视图从其所在的数据库和同一服务器上的另一个数据库中进行选择。
我收到的错误是这样的：服务器主体“proxyuser”无法在当前安全上下文下访问数据库“mydb”。
这是我尝试过的：
从第二个数据库中删除 proxyuser 并将登录名映射到数据库，如此处建议的那样。
按照此处的建议启用跨数据库所有权链接。 http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/07dcab8c-b830-4ca9-8afc-3e75772f44d3/
并且我的场景不符合Microsoft 支持文章#913422 概述了要求，该文章描述了相同的错误消息。代理使用 Sql Server 身份验证，而不是 Windows 身份验证。
有什么想法吗？
提前致谢。
I've set up multiple views in a sql server database. These views are are selected from in a stored procedure which has WITH EXECUTE AS 'proxyuser' clause in it's declaration. The select statement it executes is created dynamically, and executed with the sp_executesql stored procedure. Everything has worked correctly until it became necessary to include a join to a table in a different database in the definition of one of those views, so that the view is selecting from both the database where it resides and another database on the same server.
The error I'm getting is this: The server principal "proxyuser" is not able to access the database "mydb" under the current security context.
This is what I've tried:
Deleting the proxyuser from the second database and mapping the login to the database as recommended here..
http://sqlmusings.wordpress.com/2008/06/12/issue-server-principal-is-not-able-to-access-the-database-under-the-current-security-context/Enabling cross database ownership chaining as recommended here..
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/07dcab8c-b830-4ca9-8afc-3e75772f44d3/
and my scenario doesn't meet the requirements outlined by Microsoft Support Article #913422, which describes the same error message. The proxy uses Sql Server Authentication, not Windows Authentication.
Any ideas?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在长期从事另一个项目之后，我终于回到了这个话题。我解决了这个问题，以便不再使用WITH EXECUTE AS“proxyuser”语法。我正在连接的用户有足够的权限来执行该操作。我不知道为什么我之前没有尝试过。
I'm finally coming back to this after a long side-track working on a different project. I worked it out so that I'm no longer using the WITH EXECUTE AS 'proxyuser' syntax. The user I was connecting with had sufficient permissions to perform the action. I'm not sure why I didn't try that before.