Sql-server – Error when using OPENROWSET from a View as a nonsysadmin

extended-stored-procedureopenrowsetsql serversql-server-2012stored-procedures

[SQL Server 2012] I am running into an issue regarding the usage of a non-sysadmin account calling a view which contains a OPENROWSET call that pulls from a stored procedure. I was wondering whether anyone has a possible fix to what I am currently trying or a suggestion that can get me out of the current workaround of using OPENROWSET in a view (possibly a linked server setup?).

I currently make a call to a procedure that looks like this:

SELECT *
FROM
OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=NO;UID=accountName;PWD=accountPassword;', 
           'SET FMTONLY OFF; 
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')

and the results are returned into a view. This all works just fine and executes when running this as a sysadmin. The problem is we now wish to give a non-sysadmin user access to this view, however, after granting SELECT permissions to the user for that view and trying to access the view as that user, this error comes up:

'Access to the remote server is denied because no login-mapping exists.'

I may have narrowed this down to the fact that the view itself calls OPENROWSET. OPENROWSET is used because I call on a stored procedure that pulls data from an API and produces a rowset. The stored procedure also calls extended stored procedures so this is not something easily converted to a function.

My only current way of getting this to work, in the newly created account, is by granting it sysadmin permissions. This is obviously not the desired route to take for security reasons but it helps confirm that this should work.

There are a vast number of questions regarding SQL Server + OPENROWSET but based on a few good hours of research I cannot seem to find a solution specific to this case. Any help would be very appreciated.

I arrived at my current setup through the referencing of answers like these:

and many more.

End note, I have already considered having the stored procedure(s) update/create local tables, possibly running on a nightly schedule to make sure the data is up to date, and creating views based off of the values in those updated tables. However, it reduces much of the duplicate code and re-configuring of stored procedures if I create the view to call the stored procedure on-the-fly with the parameters I wish to send to the procedure.

Best Answer

To respond to my own question, was able to get it working using a loopback linked server and using OPENQUERY instead of OPENROWSET. The non-sysadmin account now has the controlled access it needs.

Here is the new query the view uses:

SELECT *
FROM
OPENQUERY([LOOPBACKSERVERNAME], 
           'SET FMTONLY OFF; 
            SET NOCOUNT ON;
            EXEC [My Stored Procedure] @params = ''field1,field2''
            WITH RESULT SETS
            (
                (
                    field1 NVARCHAR(1000),
                    field2 NVARCHAR(1000)
                )
            )
        ')
Related Question