Who is doing that SELECT statement?

[bdotzour]

Hi all.

I'm working on a set of sprocs to act as wrappers to our database and I want to create a new user/role to ONLY have EXECUTE privileges on these procedures.

Now, the procedures themselves are executing SELECT statments on other tables that the user does NOT have privileges to. This is why we're doing this, to prevent the users from doing arbitrary SELECTs.

My question is: This works fine if the proc does a straight SELECT statment. However, if the proc uses sp_executesql to run a SELECT statement, i get back the message that SELECT privilege is denied. Does anyone know what is going here or how to get around this so that the sp_executesql will work without granting privileges on that table?

TIA
Bryan

 

[bdotzour]

I'll take a stab at answering my own question. It appears that when you create a stored procedure that does a normal SELECT statement, that statement is executed with the permissions of the user who created the sproc.

However when the proc uses sp_executesql the SQL is executed as the user who invoked the proc. Therefore if the user doesn't have SELECT privilige on that table, the sproc will fail.