CODEWITHSUNDEEP
sql-serveropenrowset
SlipEternal
SlipEternal

Reputation: 204

Trying to avoid nested insert-exec using openrowset, but getting weird permissions error

I have a lot of databases, each with a bunch of stored procedures I need to run. These stored procedures run other procedures, which include insert exec statements. And I need to aggregate all of this data into a single dataset. I created dynamic SQL using openrowset to avoid nesting insert exec statements, and it worked for 90% of the stored procedures. All of the stored procedures are extremely similar (all using dynamic SQL to query tables local the server, but spread across several databases). All of the stored procedures that are failing include a cursor. That seems to be the only difference. Refactoring all of those stored procedures to avoid using a cursor would be a nightmare. The specific error message I am getting is this:

Cannot process the object "{procedure name and parameters}". The OLE DB provider "MSOLEDBSQL" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

If I run the stored procedure directly, it returns results. It just does not like being called via openrowset (using my Trusted Connection). Here is the Openrowset command:

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

But, the other nearly identical stored procedures all work fine with this query. I definitely have permissions to run that stored procedure, and the stored procedure definitely returns a result set with columns (and one row). Has anyone seen that error before? My searches have not been fruitful. Or is there a workaround for this?

Update 1: Here is a minimally reproducible example:

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

Upvotes: 0

Views: 1227

Answers (1)

Stewart
Stewart

Reputation: 835

You could try this to double check the linked server is correctly identifying who you are:

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

Upvotes: 0

Related Questions