This book excerpt discusses specifying execution context for stored procedure code, which allows dynamic SQL in an SP to execute as the SP owner (among other contexts). The basic syntax is:

CREATE PROCEDURE dbo.usp_DoSomething( @someParameter nvarchar(10) ) WITH EXECUTE AS OWNER AS ...

Where OWNER can be a number of different values. Obviously this does not in itself provide any security (in fact, it allows you to circumvent it by elevating privileges), but it does allow you to build some on top of this. The caller’s account can be denied SELECT access on a certain table, but can still run the SP as the owner to get back a controller result from a SELECT operation, just as in static SQL.

Potential damage from SQL injection can be minimised by having the owner (or the context in which the SP is executing) only having SELECT access (and therefore unable to inject fun things like DROP TABLE, for example). Of course, this is no excuse for not exercising appropriate levels of caution when using dynamic SQL :-)

At work we found this approach useful for simplifying complex static queries by building more efficient dynamic SQL, but still using static SQL-style security and not requiring the caller to have SELECT access on all the referenced tables.