试图避免使用openrowset嵌套insert-exec，但得到奇怪的权限错误

Question

我有很多数据库，每个数据库都有一堆我需要运行的存储过程。这些存储过程运行其他过程，其中包括插入执行语句。我需要把所有这些数据聚合成一个数据集。我使用openrowset创建了动态SQL，以避免嵌套insert exec语句，它适用于90%的存储过程。所有存储过程都非常相似(都使用动态SQL查询服务器本地的表，但分布在多个数据库中)。所有失败的存储过程都包含游标。这似乎是唯一的区别。重构所有这些存储过程以避免使用游标将是一场噩梦。我得到的具体错误信息是:

无法处理对象"{过程名称和参数}"。OLEDB提供者"msoledbsql";对于链接服务器"(null)"指示对象没有列，或者当前用户对该对象没有权限。

如果我直接运行存储过程，它将返回结果。它只是不喜欢通过openrowset(使用我的可信连接)被调用。下面是Openrowset命令:

SELECT * FROM OPENROWSET('SQLNCLI','Server={Servername};Database={DatabaseName};Trusted_Connection=yes;','exec {procedure name and parameters}')

但是，其他几乎相同的存储过程都可以很好地处理这个查询。我肯定有运行该存储过程的权限，并且该存储过程肯定返回一个包含列(和一行)的结果集。有人见过这个错误吗?我的搜寻没有结果。或者有解决这个问题的方法吗?

更新1:这是一个最低限度可复制的例子:

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO 
CREATE TABLE FakeTable(ID INT, SomeData VARCHAR(50))
INSERT INTO FakeTable(ID,SomeData)
VALUES(1,'Data1'),(2,'Data2'),(3,'Data3')
GO
CREATE PROCEDURE dbo.MyProc
AS
BEGIN
DECLARE aCursor CURSOR FOR
SELECT ID
FROM FakeTable
DECLARE @CurrID INT
DECLARE @SQL VARCHAR(MAX)
DECLARE @Results TABLE(SomeData VARCHAR(50))
OPEN aCursor
FETCH NEXT FROM aCursor INTO @CurrID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT SomeData FROM FakeTable WHERE ID = ' + CAST(@CurrID AS VARCHAR)
INSERT INTO @Results(SomeData)
EXEC(@SQL)
FETCH NEXT FROM aCursor INTO @CurrID
END
CLOSE aCursor
DEALLOCATE aCursor
SELECT *
FROM @Results
END
GO
CREATE PROCEDURE myProc2
AS
BEGIN
SELECT SomeData
FROM FakeTable
END
GRANT EXECUTE ON dbo.myProc TO [public]
GO
GRANT EXECUTE ON dbo.myProc2 TO [public]
GO
GRANT SELECT ON FakeTable TO [public]
GO
--These three all work fine and return the same data set.
EXEC myProc
EXEC myProc2
SELECT *
FROM OPENROWSET('SQLNCLI','Server={ServerName};Trusted_Connection=yes;Database={DatabaseName};','EXEC myProc2') a
/* This fails because of nested INSERT-EXEC
DECLARE @TempTable TABLE(SomeData VARCHAR(50))
INSERT INTO @TempTable(SomeData)
EXEC myProc
*/
--This generates an error as well, for some reason.
SELECT *
FROM OPENROWSET('SQLNCLI','Server={ServerName};Trusted_Connection=yes;Database={DatabaseName};','EXEC myProc') a
DROP PROCEDURE dbo.myProc
DROP PROCEDURE dbo.myProc2
DROP TABLE FakeTable
GO
sp_configure 'Ad Hoc Distributed Queries', 0;  
RECONFIGURE;  
GO 
sp_configure 'show advanced options', 0;  
RECONFIGURE;
GO 

Answer 1

你可以试着这样检查链接的服务器是否正确识别你的身份:

SELECT X.* FROM 
OPENROWSET('SQLNCLI','Server={Servername};Database={DatabaseName};Trusted_Connection=yes;', 
'SELECT SUSER_SNAME() AS LoginName;') as X