Grant Execute On Update Stored Procedure to User WITHOUT Update Rights

[cfaulkner]

SQL Server 2000 (8.0.2039)

I need to grant execute permissions on a stored procedure that updates a table to a user that does not have update permissions on the table the sproc updates. I can't get this working.

I use
grant execute on dbo.sprocname to username
and get UPDATE permission denied on object.

The funny, or not so funny thing, is that I've done this before on an insert sproc and it works fine. I've compared the two and can find no difference on the user or sproc permission between the one that works and the one that doesn't. I have spent several hours on this seemingly simple task so far.

What the heck am I missing?

Thanks,
Craig

 

[mrdenny]

Granting execute rights to the procedure should be all you need to do.

The exceptions to that are:
If the table is in a different database, and cross-database chaining is disabled.
If dynamic SQL is being used.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

who is the owner of the objects? sometimes if the object owners are different, you can get permission chain problems.

Also dynamic SQL is the most likely culprit. If you use dynamic SQl you must set permissions at the table level which is one reason why it is better not to use it.

"NOTHING is more important in a database than integrity." ESquared

 

[cfaulkner]

Ah, thank you, thank you, thank you.

It's the dynamic SQL.

Thanks, Craig