SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure 'Execute As Owner' not finding tables


Stored procedure 'Execute As Owner' not finding tables

Author
Message
JustMarie
JustMarie
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1362
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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5328 Visits: 875
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
JustMarie
JustMarie
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 1362
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.
sneumersky
sneumersky
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 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.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5328 Visits: 875
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search