Rights on sproc with dynamic sql

I have a sproc z on on which user x has execute rights.
The sproc z dynamically builds a sql statement based on input param.
The sql quey table y on which user x does not have execute rights.
When the sql is executed at the end of the sproc I get an error saying
that "user x does not have exec rights on table y".
Shouldn't it be sufficient to grant user x exec rights on sproc z?
/M
Not when you're using dynamic sql. Thats one of the problems. Dynamic sql is
executed in the security context of the user, while 'normal' sql is executed
in the security context of the sp owner (usually dbo).
Or, to bi more precise, grantable permissions are executed in the owner
context. You cant grant permission to execute dynamic sql and it is executed
as user...
MC
<job@.bla.com> wrote in message
news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>I have a sproc z on on which user x has execute rights.
> The sproc z dynamically builds a sql statement based on input param.
> The sql quey table y on which user x does not have execute rights.
> When the sql is executed at the end of the sproc I get an error saying
> that "user x does not have exec rights on table y".
> Shouldn't it be sufficient to grant user x exec rights on sproc z?
> /M
|||Hmm. Thanks. Guess I'll have to rethink my approach
On Thu, 9 Mar 2006 14:15:01 +0100, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:

>Not when you're using dynamic sql. Thats one of the problems. Dynamic sql is
>executed in the security context of the user, while 'normal' sql is executed
>in the security context of the sp owner (usually dbo).
>Or, to bi more precise, grantable permissions are executed in the owner
>context. You cant grant permission to execute dynamic sql and it is executed
>as user...
>
>MC
><job@.bla.com> wrote in message
>news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com.. .
>
|||> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
It is a common misconception that the security context is the owner. The
actual behavior is that permissions on indirectly referenced objects are not
checked at all when the ownership chain is unbroken (all objects owned by
the same user). Dynamic SQL is considered a direct object reference so user
permissions are checked just like when the ownership chain is broken.
SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
take precedence. See http://www.sommarskog.se/grantperm.html.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Not when you're using dynamic sql. Thats one of the problems. Dynamic sql
> is executed in the security context of the user, while 'normal' sql is
> executed in the security context of the sp owner (usually dbo).
> Or, to bi more precise, grantable permissions are executed in the owner
> context. You cant grant permission to execute dynamic sql and it is
> executed as user...
>
> MC
> <job@.bla.com> wrote in message
> news:gq80129hctb468t8hfbe40lahsmagad4gh@.4ax.com...
>
|||Does that mean that deny on table to table_owner woudlnt actually be
effective if the table_owner is the sp owner as well?
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
> It is a common misconception that the security context is the owner. The
> actual behavior is that permissions on indirectly referenced objects are
> not checked at all when the ownership chain is unbroken (all objects owned
> by the same user). Dynamic SQL is considered a direct object reference so
> user permissions are checked just like when the ownership chain is broken.
> SQL Server 2005 introduces EXECUTE AS but ownership chains still apply and
> take precedence. See http://www.sommarskog.se/grantperm.html.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:uZGzwm3QGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||It serves no purpose to DENY object permissions to the object owner since
the owner always has full permissions anyway. In any case, I know of no
exceptions to the ownership chain rule - permissions are not checked when
the chain is unbroken.
Hope this helps.
Dan Guzman
SQL Server MVP
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Does that mean that deny on table to table_owner woudlnt actually be
> effective if the table_owner is the sp owner as well?
>
> MC
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OmaDO03QGHA.4696@.tk2msftngp13.phx.gbl...
>
|||Ok, thanks for explaining.
MC
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:erVTCW%23QGHA.5584@.TK2MSFTNGP12.phx.gbl...
> It serves no purpose to DENY object permissions to the object owner since
> the owner always has full permissions anyway. In any case, I know of no
> exceptions to the ownership chain rule - permissions are not checked when
> the chain is unbroken.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:%23OaJoF4QGHA.4452@.TK2MSFTNGP12.phx.gbl...
>