Erro ao usar OPENROWSET em uma visualização como um não-administrador

Question

[SQL Server 2012] Estou tendo um problema com relação ao uso de uma conta que não é do administrador do sistema chamando uma exibição que contém uma chamada OPENROWSET que puxa de um procedimento armazenado . Gostaria de saber se alguém tem uma possível correção para o que estou tentando no momento ou uma sugestão que pode me tirar da atual solução alternativa de usar o OPENROWSET em uma exibição (possivelmente uma configuração de servidor vinculado?).

Atualmente, faço uma chamada para um procedimento semelhante a este:

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)
                )
            )
        ')

e os resultados são retornados em uma visualização. Tudo isso funciona muito bem e é executado ao executar isso como um administrador de sistema. O problema é que agora desejamos dar a um usuário não administrador de sistema acesso a esta visão, no entanto, após conceder permissões SELECT ao usuário para essa visão e tentar acessar a visão como esse usuário, este erro aparece:

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

Posso ter restringido isso ao fato de que a própria visão chama OPENROWSET. OPENROWSET é usado porque eu chamo um procedimento armazenado que extrai dados de uma API e produz um conjunto de linhas. O procedimento armazenado também chama procedimentos armazenados estendidos, portanto, isso não é algo facilmente convertido em uma função.

Minha única maneira atual de fazer isso funcionar, na conta recém-criada, é concedendo a ela permissões de administrador de sistema. Obviamente, esse não é o caminho desejado por motivos de segurança, mas ajuda a confirmar que deve funcionar.

Há um grande número de perguntas sobre o SQL Server + OPENROWSET, mas com base em algumas boas horas de pesquisa, não consigo encontrar uma solução específica para este caso. Qualquer ajuda será muito apreciada.

Cheguei à minha configuração atual referindo-se a respostas como estas:

• https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
• https://stackoverflow.com/questions/2896445/why-can-we-not-execute-a-stored-procedure-inside-a-function-in-sql-server
• https://stackoverflow.com/questions/916784/how-to-call-stored-procedure-in-a-view?noredirect=1&lq=1

e muitos mais.

Nota final , já considerei fazer com que o (s) procedimento (s) armazenado (s) atualize / crie tabelas locais, possivelmente executando em uma programação noturna para garantir que os dados estejam atualizados, e criando visualizações com base nos valores dessas tabelas atualizadas. No entanto, isso reduz muito o código duplicado e a reconfiguração de procedimentos armazenados se eu criar a visualização para chamar o procedimento armazenado instantaneamente com os parâmetros que desejo enviar para o procedimento.

Answer 1

Para responder à minha própria pergunta, consegui fazê-lo funcionar usando um servidor vinculado de loopback e usando OPENQUERY em vez de OPENROWSET . A conta não-sysadmin agora tem o acesso controlado de que precisa.

Aqui está a nova consulta que a visualização usa:

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)
                )
            )
        ')