Hi, All.

I have the following scenario:

There's a spefic SQL's user in a database, and this user will not have access for any table. I'll create a SP that will retrieve some data, and this is the only thing that this user will have rights.

In that SP, I'll build a dynamic query, and use the sp_executesql SP to run the query. Something like this:

declare @cSQL as nvarchar(1000)
Set @cSQL = "Select * from pubs"
exec sp_executesql cSQL

If I try to log as the mentioned user and then run this SP, SQL will say that I don't have permission to access the "pubs" object in the database. I don't have problem if I just try to run the SQL Statement directly (instead of using sp_executesql), but I can't do that, as I'm building the SQL Statement on-the-fly.

Do you guys have any idea how that could be solved?

TIA