Confused with security

I thought I understood what was happening - obviously not.
Here is my problem:
Using windows integrated security.
No users should have access to the table directly.
I have a UDF that I have granted select, insert, update, and delete on to a
created roll that has windows users added to it.
The user can select through the function ok. but when they try to insert,
they get an error complaining of lack of rights to insert.
I had to add insert rights to the table for the user to insert through the
function.
Is it becuse the function is a select statement, and it dynamically creates
the update, insert methods directly against the table and by-passes the
function and that is my problem?
Thanks for any clarification.On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:

>I thought I understood what was happening - obviously not.
>Here is my problem:
>Using windows integrated security.
>No users should have access to the table directly.
>I have a UDF that I have granted select, insert, update, and delete on to a
>created roll that has windows users added to it.
>The user can select through the function ok. but when they try to insert,
>they get an error complaining of lack of rights to insert.
>I had to add insert rights to the table for the user to insert through the
>function.
>Is it becuse the function is a select statement, and it dynamically creates
>the update, insert methods directly against the table and by-passes the
>function and that is my problem?
>Thanks for any clarification.
>
Hi Howard,
Your guess is correct - welcome to the pitfalls of dynamic SQL.
If a stored procedure references a table owned by the same userid that
owns the stored procedure, no additional check for access right to that
table is made. Each user that has rights to execute the procedure can
access that table through that procedure. Other means of access to the
table still need explicit permissions for that user on the table!
Dynamic SQL is executed in a seperate environment, just as if the user
exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
that this is started from a stored procedure. Therefor, the ownership
chain from procedure to table breaks when dynamic SQL is executed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks so much Hugo, I thought I was really lost!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:teo9j0l1fnknj0frfq3l0u6hgs2s5oh9bm@.
4ax.com...
> On Tue, 31 Aug 2004 19:03:22 GMT, Howard Carr wrote:
>
a[vbcol=seagreen]
the[vbcol=seagreen]
creates[vbcol=seagreen]
> Hi Howard,
> Your guess is correct - welcome to the pitfalls of dynamic SQL.
> If a stored procedure references a table owned by the same userid that
> owns the stored procedure, no additional check for access right to that
> table is made. Each user that has rights to execute the procedure can
> access that table through that procedure. Other means of access to the
> table still need explicit permissions for that user on the table!
> Dynamic SQL is executed in a seperate environment, just as if the user
> exeecuted the SQL from Query Analyzer. During execution, SQL is not aware
> that this is started from a stored procedure. Therefor, the ownership
> chain from procedure to table breaks when dynamic SQL is executed.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)