Secure Dynamic SQL (EXECUTE or sp_executesql)

[JD1866DSC]

Hi, I understand its good practise to grant execute permissions to your stored procedure rather than the base tables. However when using dynamic SQL in a stored procedure it runs in its own security context meaning you need to grant SELECT rights on the base tables.

Does anyone know a way round this problem? that I can still use dynamic SQL without granting SELECT permissions on the base tables

 

[mrdenny]

No, when using dynamic SQL you must grant access to the underlying tables.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

Dynamic SQL is amost always a bad idea. This is one of the reasons why. You should avoid dynamic SQL as much as possible.

Questions about posting. See faq183-874