Dynamic SQL in Stored Procedures

A small percentage of my client's stored procedures use dynamic SQL
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:

>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy