I've got a procedure which is using dynamic SQL to construct an SQL statement, and then execute the statement using sp_ExecuteSQL.
I've given permission to the login to execute the stored procedure - but, since I'm using sp_ExecuteSQL, it is failing, due to the fact that the login does not have permissions on the underlying table.
As far as my research has told me, there are two solutions to the problem:
1) Grant the user permissions to each underlying table. This means SELECT, and possibly INSERT AND DELETE depending on what the dynamic SQL is supposed to do.
2) Create the procedure with EXECUTE AS, and use a different login that has permissions on the underlying tables.
Seems to me like option #2 is the better choice, but I'd like to know if there are any risks associated. It seems to me that there shouldn't - all the login has permission to do is execute the stored procedure that I've given it access to.