Stored Procedure Ownership Chaining

Question

I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form:

CREATE PROCEDURE load_stuff
WITH EXECUTE AS OWNER AS
INSERT INTO my_db.dbo.report_table
(
  column_a
)
SELECT
  column_b
FROM data_mart.dbo.source_table
WHERE
  foo = 'bar';

These run fine when I execute the query in SQL Server Management Studio. When I try to execute them using EXEC load_stuff, the procedure fails with a security warning:

The server principal "the_user" is not able to access the database "data_mart" under the current security context.

The OWNER of the sproc is dbo, which is the_user (for the sake of our example). The OWNER of both databases is also the_user and the_user is mapped to dbo (which is what SQL Server should do).

Why would I be seeing this error in SQL Server? Is this because the user in question is being aliased as dbo and I should use a different user account for my cross-database data access?

Edit I understand that this is because SQL Server disables cross database ownership chaining by default, which is good. However, I'm not sure of the best practice in this situation. If anyone has any input on the best practice for this scenario, it would be greatly appreciated.

Edit 2 The eventual solution was to set TRUSTWORTHY ON on both of the databases. This allows for limited ownership chaining between the two databases without resorting to full database ownership chaining.

Answer 1

Why not remove EXECUTE AS OWNER?

Usually, my user executing the SP would have appropriate rights in both databases, and I don't have to do that at all.

Answer 2

There is no need to create login, you can just enable guest user in target DB.

grant connect to guest

This allows executing user to enter DB under guest context, and when "db chaining is ON access will not be checked in target DB.