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
Claudio Lassala