Permissions question

[jsteph]

Hi all,
I've never been able to get a real answer to this and I'm wondering if anyone can help.

What is the proper best practice to allow a lower-privelige level user to do higher privelige things by proxy.

A simple example is that I want to have a stored procudure that updates a table. 'UserA' does not have rights to this table but I do want to give UserA permissions to update one field in this table *only* via this stored procedure.

So I want to give him Execute permissions for the sp. I can do that, but it fails saying he doesn't have permission on the table.

So bottom line--I want to give him proxy permission to do that update in the sp. Is there any mechanism to allow this in sql-2005?

If not, then how does one go about it when a client application run by a low-privelige user has a need to update tables under which this user normally does not have rights?
Thanks,
--Jim

 

[danvlas]

So I want to give him Execute permissions for the sp. I can do that, but it fails saying he doesn't have permission on the table"

Do you, by any chance, use Dynamic SQL in your stored procedure? If yes, STOP and reconsider.


Daniel Vlas
Systems Consultant

 

[jsteph]

Dan,
Absolutely not. It's a single update statement that takes a couple of parameters, and the client app calls it using ado parameter objects, so I should be safe on injection worries.

So is it possible to make this happen?
--Jim

 

[gmmastros]

The confusing part is... If the user has execute permissions on the sproc, they shouldn't need permissions to the table. The only exception to the is with dynamic SQL.

Are there any triggers on the table that use dynamic SQL?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jsteph]

I created a new procedure to test and it works.

The problem is that the original procedure did indeed have dynamic sql--however it was built inside the stored procedure. It ran several statements of normal sql on the table and I just assumed when the error message came it was on these.

The procedure *is* called with parameters and the dynamic sql is safe in that it's what I wan't and is not modifiable by the user--but it did use the exec() statement in one spot and that was it.
Thanks,
--Jim