views:

292

answers:

2

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:

  1. Created a user called "Customer"
  2. Created the stored proc owned by that user
  3. 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...

+2  A: 

"When one user owns the source object and all target objects, the ownership chain is said to be unbroken. If different users own the target objects, the ownership chain is broken. SQL Server relies on the state of the ownership chain to determine when to check permissions.
(snip)
If the ownership chain is broken, SQL Server checks permissions on each branch of the chain owned by a different user. Only those statements where the user has the necessary permissions will be executed, and the remaining statements will get an "Insufficient Permissions" error. In this way, SQL Server allows object owners to retain control over permissions."

Quoted from Using Ownership Chains

To apply this to the current scenario, when the Customer.InsertOrder proc attempts to insert into the dbo.Order table, this breaks the ownership chain, and SQL checks to see if the original user (WebUser, in this case) has permission to perform the insert.

So WebUser (not Customer) will need permission to INSERT into dbo.Order (either explicitly, or via db_datawriter). All permissions can be removed from Customer, if desired.

AlexKuznetsov
Explicitly grant insert permissions on your table to user Customer.
AlexKuznetsov
A: 

It's the Customer user. It needs datawriter access to the Order table.

Ben Breen
But the Customer user already is already a member of the database-level db\_datawriter role. Shouldn't that be enough?
BradC
What happens if you reference the Order table as dbo.Order from the InsertNewOrder sp?
Ben Breen
Not sure. Don't want to do this if I don't have to, as this is only 1 of a couple dozen procs (I simplified for the sake of the question). You are correct, though, the stored proc doesn't use a fully-qualified table name.
BradC
Nope. Was the WebUser, not the Customer user.
BradC
Apols. I was sure sp's ran in the user context of their owner. I thought that you could prevent access to a table from a user directly but give access to some data via an sp. Maybe it's the lowest rights of the calling connection and owner or something..? But doesn't really make sense...
Ben Breen
One of the things I ran into was an option to "EXECUTE AS OWNER" option, but I think that only applies to SQL 2005 and above...
BradC