title: Problems running a stored procedure owned by another user
body: <p>We have a 3rd-party app that wants to call a stored proc using SQL 2005-style "schema" syntax:</p>
  
  <pre><code>Customer.InsertNewOrder
  </code></pre>
  
  <p>This db is still on SQL 2000, though, so we're trying to make it "look" like a SQL 2005 schema, and still work right (with minimum necessary permissions). So we:</p>
  
  <ol>
  <li>Created a user called "Customer"</li>
  <li>Created the stored proc owned by that user</li>
  <li>Granted the web user "execute" rights on the stored proc (via a role)</li>
  </ol>
  
  <p>Granting our web user execute rights is usually all that is takes to run dbo-owned procs (like "dbo.InsertOrder"). We don't usually have to grant explicit permissions on the underlying tables. But it doesn't seem to work in this case. We're getting an error:</p>
  
  <pre><code>INSERT permission denied on object "Order"
  </code></pre>
  
  <p>So what am I doing wrong?</p>
  
  <p>How can I tell <em>what</em> user is being denied here. Is it my WebUser? Or is it "executing as" the <strong>Customer</strong> user, and the Customer user needs additional permissions? (I tried granting additional permission to the Customer user (db_datareader and db_datawriter, even tried db_owner), but that didn't seem to help. db_owner isn't an option in production, anyway.)</p>
  
  <p>I suppose I can grant explicit INSERT permissions on the necessary table, but why would this be necessary in this case, when it isn't the case for a DBO owned proc?</p>
  
  <p><strong>EDIT:</strong><br>
  I've simplified for the sake of this question. In reality, I have several dozen stored procs under two different "schemas" that insert, update, and select from many tables. So hoping to not have to manually itemize permissions on all, if I can help it...</p>