July 22, 2005 at 2:50 pm
hi,
If I have a VIEW or STORED PROC ( refer to as main) and within these objects I use any objects I like ( refer to as subs) .
So if I give the main object permission a GRANT, l sub objects that are used within main object will be access as required with no limitations.
Say dbo.procTEST0 uses vwTEST1(view) and fnTEST2(function).
User dbRole "A", has EXEC on procTEST0 granted, but the vwTEST1 and fnTEST2 have no positive permissions (ie REVOKE state) for "A" : procTEST0 should run fine correct ?
Is there are any time when this is not so ???
July 22, 2005 at 3:32 pm
Edit:
If your procedure uses dynamic sql then you will need to grant specific select/update on underlying tables/views..
Otherwise you should be okay.
July 22, 2005 at 4:14 pm
I do have dynamic SQL used, that explains a few things...
Cant find anything on this in BOL, can you ??
July 22, 2005 at 11:27 pm
By granting a user the rights to execute a stored procedure or select from a view, any objects accessed by the view/SP are accessed using the rights of the view/SP owner. However... Dynamic SQL is evaluated in the context of the current SQL DB user, not the owner of the view/S that is currently executing.
I'm sure it is in BOL somewhere. Look up help for the exec or sp_executesql topics.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply