Permission issues in Dynamic Querries

I am trying to run this piece of SQL code in an SP, as a database user called 'xsndev'.
--
DECLARE @.testvar VARCHAR(1000)
SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0, player2hole7stroke=0 WHERE linksgameid=0'
exec(@.testvar)
GO
--
And I am getting this error:
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV', owner 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV', owner 'dbo'.
--
Whereas if I run the same code with login 'sa' which is the dbo of the database, SP runs smoothly.
I have give permission to 'xsndev' user on the above table and the stored procedure, but still same error occurs.
Is there any other configuration or setting I need to change for the dynamic sql to work?
Thanks,
RohitThis is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
--
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...
> I am trying to run this piece of SQL code in an SP, as a database user
called 'xsndev'.
> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,
player2hole7stroke=0 WHERE linksgameid=0'
> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> --
> Whereas if I run the same code with login 'sa' which is the dbo of the
database, SP runs smoothly.
> I have give permission to 'xsndev' user on the above table and the stored
procedure, but still same error occurs.
> Is there any other configuration or setting I need to change for the
dynamic sql to work?
> Thanks,
> Rohit
>|||Hi OJ,
I dont think I understand your answer entirely. What is the base object
you are talking about? I have given permissions to the 'xsndev' user on the
table 'XSN_Links_Games' Table.
Thanks,
Rohit
-- oj wrote: --
This is by design for dynamic query. You have to give permission on the base
object(s) to the executing user(s).
--
-oj
http://www.rac4sql.net
"Rohit" <anonymous@.discussions.microsoft.com> wrote in message
news:8A6AA900-8267-4A4C-B0CE-0731AEDE6CB1@.microsoft.com...
> I am trying to run this piece of SQL code in an SP, as a database user
called 'xsndev'.
> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,
player2hole7stroke=0 WHERE linksgameid=0'
> exec(@.testvar)
> GO
> --
> And I am getting this error:
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',
owner 'dbo'.
> --
>> Whereas if I run the same code with login 'sa' which is the dbo of the
database, SP runs smoothly.
>> I have give permission to 'xsndev' user on the above table and the stored
procedure, but still same error occurs.
>> Is there any other configuration or setting I need to change for the
dynamic sql to work?
>> Thanks,
> Rohit
>>