Dynamic SQL and the security context

[emilg]

The environment

I'm operating an ASP.NET application working with SQLServer 2000 backend. The application is working on the database only through stored procedures. There is no SQL code in the application.

For security reasons I created a special login and a special role for the application's connection string. The role grants only execution permissions on the required stored procedures and denies access to any other database objects (no SELECT/UPDATE/DELETE on the tables). In this way any attack using that account (direct login or sql injection) is limited.

The problem

The system works perfectly except 1 problem. The procedures that use dynamic SQL (with sp_executesql) are not working:

Exec Entities @TableName = 'TableTest', @TableColumn = 'ColumnX'
----------------------------------------------

Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'TableTest', database 'Production', owner 'dbo'.

This procedures, which have proper rights, contains code like:

CREATE PROCEDURE Entities
(
@TableName nvarchar(50),
@TableColumn nvarchar(100)
)
AS

declare @string nvarchar(1000)
set @string = 'select ' + @TableName + 'ID, ' + @TableColumn + ' as ' + @TableName + ' 
from ' + @TableName
exec sp_executesql @string

It seems that the "exec sp_executesql" statement is executed in another security context that the one that the procedure that encapsulates it runs in.

Do you have any suggestion on how to solve/avoid this issue?

[rmiao]

Is owner of the stored procedure dbo?

[skhanal]

Yes, the sp_executesql will not use ownership chain. The user executing it needs direct access to the table.

http://support.microsoft.com/default...B;EN-US;301299

[emilg]

Yes, owner is dbo everywhere.

Tks, skhanal, very useful link! I'll follow the suggestions there.