"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:A0952FDA-CC2E-47FB-AD89-B88C466F656D@microsoft.com...
>> Is there a way I can avoid granting the user Select access to the
>> table, still allowing him to execute the stored proc.
>
> In SQL Server 2000, you must grant execute permissions to the underlying
> objects because dynamic SQL breaks the ownership chain. In SQL 2005, you
> can either sign the proc with a certificate or specify EXECUTE AS so than
> an alternate security context is used when the ownership chain is broken.
>
> See http://www.sommarskog.se/dynamic_sql.html for a more complete
> discussion.
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "donet programmer" <arpit.00@gmail.com> wrote in message
> news:1181354346.164080.38660@q69g2000hsb.googlegroups.com...
>>I am writing a stored procedure which executes a dynamic sql statement
>> on a certain table.
>> Something similar to below
>>
>> Create Procedure dbo.myproc
>> As
>>
>> -- create dynamic sql query
>> Declare @query varchar(4000)
>>
>> set @query = 'Select * from TableA'
>>
>> --
>> --
>> --
>>
>> exec(@query)
>>
>> GO
>>
>> I want to grant user exec rights on this stored procedure. But when I
>> try executing query, I get error saying access to TableA is denied. I
>> gave user Select access on the table, after which I was able to
>> execute the stored procedure.
>>
>> Is there a way I can avoid granting the user Select access to the
>> table, still allowing him to execute the stored proc.
>>
>