Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored procedure 'Execute As Owner' not finding tables Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 12:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:27 AM
Points: 277, Visits: 618
Short version:

I've got stored procedures in one database that aren't seeing tables in another database. The stored procedures have the EXECUTE AS OWNER option and the server/databases have cross db ownership chaining enabled. I created everything on the server.

Long version:

I've got a habit of putting truncate statements into stored procedures and using the WITH EXECUTE AS OWNER option to allocate permissions. That way the unattended server account doesn't have DDL (ALTER) permission. I don't like unattended accounts having any DDL permission. I'm funny that way.

Anyway. New 2012 installation and I created all the databases and objects. Everything is under my account. When I try to run a cross database sproc to truncate a table I get the error that the table cannot be found. When I run the truncate statement myself in SSMS (the same one in the stored procedure) it runs just fine.

I do have cross db ownership chaining enabled at the server and confirmed with the offending databases.

EXEC sp_configure 'cross db ownership chaining', '1';
RECONFIGURE;
EXEC sp_configure;

After running this code the stored procedure, which does a single truncate on what's currently an empty table, runs for a long time before I canceled it. It should be pretty much instant. The same code runs just fine when in the same database from the sproc call.

I'm storing all the processing stored procedures in the staging database to keep them tidy. So there's going to be a lot of cross database stuff going on.

Can someone shed light on what permission problem I'm having? At a previous job they had a special role for executing stored procedures but in this case it's kind of overkill unless it's necessary. The only stored procedures that will use the EXECUTE AS will be the truncates since everything else should have the correct permission by default. I haven't tried any of the ones with DELETE statements yet because I'm working through the process and problems.

TIA for your help.
Post #1536078
Posted Wednesday, January 29, 2014 1:15 PM This worked for the OP Answer marked as solution


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:28 PM
Points: 817, Visits: 743
When you use EXECUTE AS for a stored procedure you impersonate a user, and when you impersonate a user you are sandboxed into the current database. There are ways to open the sandbox, but they can lead to security holes. A much better strategy is to use certificate signing. This permits you to assign permissions to stored procedure much more granually, and you don't run into all the side effects with EXECUTE AS.

In this article on my web site, I discuss both certificate signing and impersonation in detail: http://www.sommarskog.se/grantperm.html.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1536090
Posted Wednesday, January 29, 2014 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:27 AM
Points: 277, Visits: 618
Erland - I read your page several times before posting the problem/question/plea.

At a previous employer this is how we got 'around' the permission situation. There was also a role that I didn't create so I don't know what permissions were associated but was called 'xx_execprocs'. I think that there was a user ID associated with permissions based on the code but this was all handled by the DBA team. I'm working as an accidental DBA now so I need to learn all this kind of stuff.

Here's the code that was part of the stored procedure templates (masked):
IF USER_ID('xx_execprocs') IS NOT NULL
GRANT EXECUTE ON dbo.sproc TO xx_execprocs;
ELSE
RAISERROR('Warning: User Role "xx_execprocs" Does Not Exist In This Database!', 16, 1);
GO

Reading this and your article will I need to create a user without a logon that has permissions to do .. what? There was never a problem going across databases using the OWNER in my previous environment.
Post #1536095
Posted Wednesday, January 29, 2014 1:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:12 PM
Points: 2,148, Visits: 487
EDIT: NM......just read this again and saw ownership chaining has been covered.

We use Execute AS LOGIN to get around some of the issues, but, depending on your environment, that can make things risky as well depending on your situation.
Post #1536097
Posted Wednesday, January 29, 2014 1:42 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:28 PM
Points: 817, Visits: 743
I don't what this xx_execprocs did, but maybe they had EXECUTE AS = 'xx_execprocs' in some parent procedure. As they grant EXECUTE permission, I suspect that they had a broken ownership chain somewhere as well. In any case, that was then, this is now.

And now, you should create a certificate, create a user from that certificate, grant that user ALTER on the table to be truncated, and sign the procedure with the certificate. The simplest is to put it all in the file that holds the stored procedure (I'm assuming that you have all under version control.) There is no need to have the password in the file - use a guid() as a throwaway password. There is a script in my article to demonstrates how to this for server-level permissions.

If you are OK with database chaining, you can stay with that. But my article also includes an example how to cross-database access with help of certificates.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1536100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse