Permission issues in Dynamic Querries

I am trying to run this piece of SQL code in an SP, as a database user calle
d 'xsndev'.
--
DECLARE @.testvar VARCHAR(1000)
SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0, player2hole
7stroke=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', own
er 'dbo'.
Server: Msg 229, Level 14, State 1, Line 1
UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV', own
er 'dbo'.
--
Whereas if I run the same code with login 'sa' which is the dbo of the datab
ase, SP runs smoothly.
I have give permission to 'xsndev' user on the above table and the stored pr
ocedure, 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...

quote:

---

> I am trying to run this piece of SQL code in an SP, as a database user

---

called 'xsndev'.

quote:

---

> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,

---

player2hole7stroke=0 WHERE linksgameid=0'

quote:

---

> 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'.

quote:

---

> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.

quote:

---

> --
> Whereas if I run the same code with login 'sa' which is the dbo of the

---

database, SP runs smoothly.

quote:

---

> I have give permission to 'xsndev' user on the above table and the stored

---

procedure, but still same error occurs.

quote:

---

> Is there any other configuration or setting I need to change for the

---

dynamic sql to work?

quote:

---

> 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...

quote:

---

> I am trying to run this piece of SQL code in an SP, as a database user

---

called 'xsndev'.

quote:

---

> --
> DECLARE @.testvar VARCHAR(1000)
> SET @.testvar = 'Update XSN_Links_Games SET player1hole7stroke=0,

---

player2hole7stroke=0 WHERE linksgameid=0'

quote:

---

> 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'.

quote:

---

> Server: Msg 229, Level 14, State 1, Line 1
> UPDATE permission denied on object 'XSN_Links_Games', database 'XSNDEV',

---

owner 'dbo'.[QUOTE]
> --
database, SP runs smoothly.[QUOTE]
procedure, but still same error occurs.[QUOTE]
dynamic sql to work?[QUOTE]
> Rohit