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.

The Setup

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

The Testing

From the Stage database, 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'.

Screw it! 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.

...

...

...sad.

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 ServiceAccount one-by-one.


Courtesy Cleanup Script

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go
  • Hi @srutzky - I did comment earlier, but it was premature. After reading your two linked answers to greater depth and reviewing the scripts, I answered my own question. Sorry for the confusion! I'm vetting out some alternate methods now. 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. Another guy on my team is vetting the TRUSTWORTHY option a little more this morning & I'm trying out a sort of "poor-man's service broker" but I don't know if that's for this thread ;-) – Peter Vandivier Apr 21 '17 at 14:47
  • @srutzky - I've modified the problem statement. 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 stepped away from cert-signing because it seemed from examples that I'd still need to sign tables/triggers/schemas in the production area individually (which seems like more work long-term). 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 ServiceAccount one-by-one. – Peter Vandivier Apr 21 '17 at 15:41
  • Peter: I have copied some of your comments here into the question and have updated my answer to address the clarified goal(s). We should probably clean up these comments as they are no longer necessary. I will delete mine if you can please delete yours. Once they are gone I will delete this comment :-). – Solomon Rutzky Apr 21 '17 at 16:39

Correct: Impersonation / EXECUTE AS, by default, is quarantined to the initial database. In order to get around this you need to do either set the initial database to TRUSTWORTHY ON (not a great option, so should be viewed as a last resort), or create a Certificate that can be in both the initial DB and the destination DB that can be used to link the desired permissions (and then you don't need Impersonation at all).

I have several examples of doing this across various answers here on DBA.StackExchange. Try these first:

Now, given the new information added to the question regarding:

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.

and:

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 ServiceAccount one-by-one.

the direction to take can be narrowed down. Usually the goal is to be as granular as possible with the permissions, so it sometimes takes some extra steps to get to a least-privileged setup. But that doesn't mean that you can't take fewer steps to be less granular when the situation calls for it.

One of the (several) wonderful aspects of module signing is that you are effectively giving permissions to the code, not to people. So you can control the who can make use of the permissions by properly setting permissions on whatever code (i.e. Stored Procedures, Functions, Triggers, etc) is being signed.

Assuming you want both Server/Instance -level and Database-level permissions, even if you don't end up using most of what will be allowed, you can do the following:

  1. Create a Certificate in [master].
  2. Create a Login from that Certificate.
  3. Add the Certificate-based Login to the sysadmin Fixed Server Role.
  4. Switch to the Stage DB
  5. Re-create the same Certificate in this DB (either by backing it up to a file on disk OR by extracting both the Certificate and the Private Key to variables or a local temporary table as shown in my other answers).
  6. Sign the Stored Procedure with this Certificate via ADD SIGNATURE.

That's it! The Stored Procedure, regardless of who executes it, can do anything it was coded to do. And if that code changes, you will need to do the ADD SIGNATURE again. But the Certificate-based Login cannot be Impersonated, so nobody can use it to get "sa" privileges. And, if you use "encrypt by password" when creating the Certificate and do not give that password to anyone, then nobody will be able to sign other modules with it.

In this setup, the only on-going administration would be re-signing the Stored Procedure if you keep changing it.


Also, regarding your final attempt (i.e. alter authorization on object::spLoadStageToProd to sa;): that doesn't work because "sa" is a Login (Instance / Server -level), not a User (Database-level). So you can only grant permissions on database-level items to principals found in sys.database_principals.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.