January 29, 2014 at 12:34 pm
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.
January 29, 2014 at 1:15 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
January 29, 2014 at 1:31 pm
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.
January 29, 2014 at 1:36 pm
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.
January 29, 2014 at 1:42 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply