Revoke Table, Grant Execute on Proc, but still get Permission Deni

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JasonRhinehart - 07 Jul 2008 15:19 GMT

All,
I'm using SQL 2000 and have:
* created a role
* granted execute to my procs for that role
* revoked on tables being accessed via the procs
* added my db user to the role

When I try to exec the procs, I get problems accessing the underlying
tables. I don't want any db user to have direct access to these tables, only
permission to exec procs. All objects are owned by dbo.

Can anyone help explain what I'm doing wrong?
Thanks,
Jason

Reply to this Message

Hugo Kornelis - 07 Jul 2008 20:41 GMT

>All,
>I'm using SQL 2000 and have:
[quoted text clipped - 10 lines]
>Thanks,
>Jason

Hi Jason,

Are the stored procedures owned by dbo as well, or only the tables and
views?

Is any dynamic SQL used in your stored procedures?

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message