Re: SP_EXECUTESQL Security in 2005

DigHazuse (DigHazuse@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Thank you for the link ... I had read that link before and it was helpful.

However, I believe I already understand why it is failing (my take):
Since the Impersonating User only has access to run a SP, and since
SP_EXECUTESQL is really building/creating and running a separate SP it
does not have access to run it. (The error is receives is that is does
not have access to the Object "Whatever Table"), This makes sense to
me.

My question really is more of a:
Since the DBA only wants to grant access to the Impersonating User to
execute the SP (which is understandable) and since I want to (in certain
occasions) create Dynamic SQL (SP_EXECUTESQL) .... how can we make both
situations work where we're both happy?

First, I don't really know what your impersonating user comes from, but
I suppose that is specific to your setup. Maybe you could give some more
details, in case this could be relevant for the issue?

The solution I recommend in my article is to sign the procedures with a
certificate. Any reason this would not work for you? Of course, you would
still need to convince your DBA that this is a sound way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.