This behavior is by design. Dynamic SQL gets executed in the context of the user who executes the stored procedure, basically it's the same as executing it directly. Otherwise it would create a huge security hole and an easy target for SQL injections. If let's say there was a stored procedure that just executes whatever string you pass in, anybody with the rights to execute the procedure would be able to execute any SQL statement, using the privileges of the user that created the procedure.

Roman


>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