Just a little side comment. If you want to avoid having to grant permissions to the explicit tables, views etc used in the dynamic query, you can sign the procedure with a certificate that is linked to a user with permissions on the explicit objects in the dynamic SQL.

http://msdn.microsoft.com/en-us/library/ms181700.aspx

The downside is that the signature is removed if you ALTER the procedure, and you have to sign it again.

But if you are using any kind of automated deployment, then you can add the signing steps in there.

We have used it successfully on many procedures with dynamic sql.