I would like to enable an application that dumps a big ol' heap of data into a Staging area to be able to kickoff the stored procedure that transforms & loads the data into the Production area. If possible, I'd like to not grant the application any access to the production area. Unfortunately,
EXECUTE AS seems to be failing me. For reference, the DBs in question are running on
2008 R2, but I'm recreating the behavior on my
2016 localhost. As well, the service account in question is a domain credential, but I've recreated the behavior with a SQL Auth credential for portability.
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
exec spLoadStageToProd; succeeds. Predictably,
exec as login = 'ServiceAccount'; exec spLoadStageToProd; fails with the error...
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'.
Okay, so I'll try
execute as owner so that the proc has the permissions it needs to do the work but the (developer who has the password for)
ServiceAccount doesn't get to muck about in my Production area.
alter proc spLoadStageToProd with execute as owner as begin insert Prod.dbo.tbl1(i) select checksum(newid())%100; -- random number end; go
Shoot! Well now a simple test from Staging of
exec spLoadStageToProd; returns the error:
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" is of course me: the doof with
sysadmin server role...
Well that's weird enough... I tried
alter authorization on object::spLoadStageToProd to dbo; but the same error (Msg 916) pops up. What's more...
exec as login = 'ServiceAccount'; exec spLoadStageToProd; now returns...
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'.
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.
So... clearly I'm misunderstanding something fundamental. I tried enabling cross-db ownership chaining just in case that was the sticky wicket but no luck. After fruitless googling and a quick scan of these two dba.se questions, I'm pretty stuck. Where am I going wrong?
How do I get this SProc to run on elevated cross-database permissions regardless of who calls it?
The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on.
If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of who calls it, that's the end-goal.
I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the
Courtesy Cleanup Script
revert; use [master] go drop database Prod,Stage; drop login ServiceAccount; go