title: Stored Procedure Ownership Chaining
body: <p>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:</p>
  
  <pre><code>
  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';
  </code></pre>
  
  <p>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:</p>
  
  <p><em>The server principal "the_user" is not able to access the database "data_mart" under the current security context.</em></p>
  
  <p>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).</p>
  
  <p>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?</p>
  
  <p><strong>Edit</strong>
  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.</p>
  
  <p><strong>Edit 2</strong>
  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.</p>