We have a 3rd-party app that wants to call a stored proc using SQL 2005-style "schema" syntax:
Customer.InsertNewOrder
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:
- Created a user called "Customer"
- Created the stored proc owned by that user
- Granted the web user "execute" rights on the stored proc (via a role)
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:
INSERT permission denied on object "Order"
So what am I doing wrong?
How can I tell what user is being denied here. Is it my WebUser? Or is it "executing as" the Customer 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.)
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?
EDIT:
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...