What I was able to discover is that the solution I was seeking wasn't available via SQL Server 2005. I ended up implementing user based security rather than the sproc based security I hoped was available.
I checked out that link, but didn't see the relevance of Dynamic Management Views to my security implementation dilemma.
Thanks,
Carleton
(This was the model I hoped existed):
I was very much hoping that security could be managed within the context of a stored procedure (through the EXECUTE AS or other method) for the following reason:
If you could give a sproc all the access it needs without giving the same level of access to the user/login executing it, it would drastically reduce the abilities a user/login needs to the system. If you could limit the world's access to only running specific stored procedures, this would be a tighter ship and potentially a simpler security model to administer.
For example, instead of giving update access to table TABLE1 to login LOGIN1, you would give update access to the sproc updating TABLE1. That way you know that LOGIN1 will not be able to perform updates not intended--they can only execute the relevant procedure. If I understand the available security options, LOGIN1 must have update access to TABLE1 in order to execute any procedure they call which updates TABLE1--and there are no ways around this? (outside of using the service broker...not an option for calling every sproc)