Revoking and Granting permissions.. SQL Security Bug\Issue ?

I am getting a very strange issue occurring when granting
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?