Eu gostaria de habilitar um aplicativo que despeja um grande e velho heap de dados em uma área de teste para poder iniciar o procedimento armazenado que transforma e carrega os dados na área de produção. Se possível, gostaria de não conceder ao aplicativo nenhum acesso à área de produção. Infelizmente, EXECUTE ASparece estar falhando comigo. Para referência, os bancos de dados em questão estão sendo executados no 2008 R2, mas estou recriando o comportamento no meu 2016localhost. Além disso, a conta de serviço em questão é uma credencial de domínio, mas recriei o comportamento com uma credencial de autenticação SQL para portabilidade.

A configuração

use [master]
create database Prod;
create database Stage;
create login ServiceAccount with password='Password1234';
go
use Prod
create table tbl1 ( i int );
insert tbl1 select 1;
go
use Stage
create user ServiceAccount for login ServiceAccount; 
go
create proc spLoadStageToProd as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go
grant execute on spLoadStageToProd to ServiceAccount;
go

O Teste

A partir do Stagebanco de dados, é bem- exec spLoadStageToProd;sucedido. Previsivelmente, exec as login = 'ServiceAccount'; exec spLoadStageToProd;falha com o erro ...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Ok, então vou tentar execute as ownerpara que o proc tenha as permissões necessárias para fazer o trabalho, mas o ( desenvolvedor que tem a senha para ) ServiceAccountnão pode mexer na minha área de produção.

alter proc spLoadStageToProd
with execute as owner
as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go

Atirar! Bem, agora um simples teste do Staging of exec spLoadStageToProd;retorna o erro:

Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35]
The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context.

"DOMAIN\peter"é claro que eu: o doof com sysadminfunção de servidor ...

Bem, isso é estranho o suficiente... Eu tentei, alter authorization on object::spLoadStageToProd to dbo;mas o mesmo erro (Msg 916) aparece. E mais... exec as login = 'ServiceAccount'; exec spLoadStageToProd;agora volta...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Foda-se! alter authorization on object::spLoadStageToProd to sa;...

Msg 15151, Level 16, State 1, Line 41
Cannot find the user 'sa', because it does not exist or you do not have permission.

...

...

... triste .

Então... claramente estou entendendo mal algo fundamental. Eu tentei habilitar o encadeamento de propriedade cross-db apenas no caso de ser o postigo pegajoso, mas sem sorte. Depois de pesquisar infrutíferas e uma varredura rápida dessas duas perguntas dba.se, estou muito preso. Onde eu estou errando?

Como faço para que este SPoc seja executado em permissões elevadas entre bancos de dados, independentemente de quem o chama?

O projeto que precipitou essa questão é uma depreciação iterativa, então acho que a assinatura de certificados pode acabar sendo uma sobrecarga administrativa bastante grande com o passar do tempo.

Se for possível usar esse proc armazenado como um wrapper para executar ações restritas independentemente de quem o chama, esse é o objetivo final.

Estou confiante de que o SProc não pode ser modificado indevidamente, então qualquer coisa que seja executada de dentro dele pode ser altamente permitida - na esperança de que eu não precise administrar permissões para o ServiceAccountum por um.

Script de limpeza de cortesia

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go