Permissions

Hi:
Actually I have a store proc which pulls data from a table. I have given
execute permission to a user on this store proc. Since owner of store proc
and table are the same (dbo), I do not need to give select permission on the
table for this user.
But i am having problem when I use dynamic sql to build a query string and
execute this sql. It then gives an error that permission denied on this
table for this user. I think It starts a new session when you build a
querystring (Select * from Table), it checks for permission on that table
because the ownership chain breaks.
If you know of any workaround to this problem, please let me know becuase I
do not want to give select permission for this user. It should be executed
thru the stored proc.
ThanksYou will need to re-write the stored procedure to not use dynamic SQL,
which requires the user to have permissions on the base tables.
--Mary
On Thu, 12 Aug 2004 14:09:03 -0700, Sal
<Sal@.discussions.microsoft.com> wrote:

>Hi:
>Actually I have a store proc which pulls data from a table. I have given
>execute permission to a user on this store proc. Since owner of store proc
>and table are the same (dbo), I do not need to give select permission on th
e
>table for this user.
>But i am having problem when I use dynamic sql to build a query string and
>execute this sql. It then gives an error that permission denied on this
>table for this user. I think It starts a new session when you build a
>querystring (Select * from Table), it checks for permission on that table
>because the ownership chain breaks.
>If you know of any workaround to this problem, please let me know becuase I
>do not want to give select permission for this user. It should be executed
>thru the stored proc.
>Thanks|||Sal,
There is one work-around I have found. If you are trying to insert
data into a permanent table via dynamic sql, first create a temporary
table with the same structure as the destination permenant table.
Insert into the temporary table via the dynamic statement.
Then, with non-dynamic sql, insert from the temp table into the
permanent table.
Hope this helps.
Sal wrote:
> *Hi:
> Actually I have a store proc which pulls data from a table. I have
> given
> execute permission to a user on this store proc. Since owner of
> store proc
> and table are the same (dbo), I do not need to give select permission
> on the
> table for this user.
> But i am having problem when I use dynamic sql to build a query
> string and
> execute this sql. It then gives an error that permission denied on
> this
> table for this user. I think It starts a new session when you build
> a
> querystring (Select * from Table), it checks for permission on that
> table
> because the ownership chain breaks.
> If you know of any workaround to this problem, please let me know
> becuase I
> do not want to give select permission for this user. It should be
> executed
> thru the stored proc.
> Thanks *
lxstewart
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message952424.html