Permissions on Dynamic SQL

[ExcelMan]

I have some code where I create some SQL and execute it with an ADO
Command object. Problem is that for some users the table I am updating
is not one they have permission to update (and they cannot have
permission to directly update).

I know that for stored queries I can run "WITH OWNERACCESS OPTION" and
allow the query to be executed as if it was the table owner running the
query, but it doesn't work to just add that clause to the dynamic SQL
statement.

Is there a way I can allow a user to execute a dynamically created SQL
statement as if he had permissions to the table?

Thanks.

 

[Brendan Reynolds]

I don't believe there is, no. Two potential alternatives are to use a saved
parameter query and assign values to the parameters at runtime instead of
building the SQL at run time, or to save a 'dummy' OWNERACCESS query, and
change the SQL of that query at runtime.

The parameter query is more secure, being less vulnerable to SQL-injection
attacks.