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

Actually DBO is a role (you can consider it as a group of users), not a user in himself. (Unless you can connect to SQL SERVER using dbo:passwordfordbo it's not a user).

Usually, in the wonderful world of Sql Server, if you grant userX right to execute storedprocY then X gets the right to perform all the task Y contains even if he doesn't have all the permission on all the objects used in Y.

That's an extremely useful feature to encapsulate business logic in stored procedure. (Your user have NO access on the table but they do can EXECUTE one stored proc).

When we talk about "ownership chaining" it means the following (please correct me if I am wrong though) - If ownership chaining is disabled : the right to execute procedureX will work as long as all the required objects are in the same database - Of chaining is enabled : That "privilege" will expands towards all databases.

Hope that helps,

Answer 3

Actually the answer from matdumsa point me to the right direction. I created a new login and made it owner of the proc in one DB and the table in the other DB. Then another login with permission to execute the stored proc. Enabled cross database ownership chaining and executed the proc. It worked like a charm! Before, DBO was owner of everything, databases, stored procs and tables. Also had another user with execute permissions on the stored proc. In that case, enabling cross database ownership chaining didn't make any difference.