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.
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';
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.