Re: Granting EXEC for read only SPs

Felix,

Isn't that what I said? It is what I meant.

The alternate idea was to emphasize the difficulty of trying to work around
clearly understanding which stored procedures the user should have rights to
execute.

RLF

"Felix Albert" <felix.np@xxxxxxxxx> wrote in message
news:735EFA72-0866-41CC-8060-8070D41A88F6@xxxxxxxxxxxxxxxx
Even in the case when the user have no rights to insert , delete or update
a table directly , if the user has the EXECUTE permission granted for a
stored proc that inserts, deletes or updates that table will work without
errors.... the correct approach is to assign the EXEC permission only to
the stored procs that don't modify the data... it looks like you are
actually trying to identify the stored proc exec permissions "a
posteriori".

Regards,
Felix


"Russell Fields" <russellfields@xxxxxxxxxx> wrote in message
news:OiEXEr#pIHA.4476@xxxxxxxxxxxxxxxxxxxxxxx
BGL,

The whole point of granting a person rights to a stored procedure is for
them to be able to run a step that they normally cannot run. So, your
problem is that the stored procedure is running under its own rights
(probably db_owner) instead of the user to who you gave the
denydatawriter permission. See discussions on ownership chaining:
http://msdn2.microsoft.com/en-us/library/ms188676.aspx

Therefore there is no way to do what you are trying. You need to know
what a stored procedure is doing before granting rights to it.

If you do not want to individually grant rights to the 'harmless' stored
procedures, you could, I suppose, go through your stored procedures to
find every one that does updates, inserts, and deletes to your data (but
not to tempdb tables or to table variables). Once you have done that,
you could add a line of code at the start of each of those sprocs to do:

IF IS_MEMBER('db_denydatawriter') = 1
RETURN (1)

FWIW
RLF

"BGL" <BGL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FA9FC969-79B8-4318-AE86-7F925300D729@xxxxxxxxxxxxxxxx
Is there any way to grant EXEC on all SPs to a user or role where the
SPs
don't modify user tables?

I created database role 'X' and granted EXEC permission for all SPs.
Then,
I created a database user, added it to the role 'X' to inherit the EXEC
permissions, AND ALSO added the user to the fixed database role
'denydatawriter'.

I was hoping this scenrio would lead to a run-time error when the SPs
that
modify data were invoked. Instead, all SPs execute successfully,
including
the ones that modify data. Any workaound to accomplish this? TIA.






.

Relevant Pages

  • Re: I still dont get it.
    ... > You can create a stored procedure to call xp_cmdshell to run a command. ... > though the originator has no such rights. ... > Russell Fields ... >> A has rights to sysadmin sproc, if I pass in the table to be created as ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... Can you grant an EXECUTE permission on stored procedure? ... I have came to the maturity level to stop using the sa account when I ... I mean it is unrealistic to have to manually set for each SP the rights ...
    (microsoft.public.sqlserver.security)
  • Re: Granting EXEC for read only SPs
    ... problem is that the stored procedure is running under its own rights ... a stored procedure is doing before granting rights to it. ... I created database role 'X' and granted EXEC permission for all SPs. ...
    (microsoft.public.sqlserver.security)
  • Re: I still dont get it.
    ... You can create a stored procedure to call xp_cmdshell to run a command. ... can be the sysadmin. ... though the originator has no such rights. ...
    (microsoft.public.sqlserver.security)
  • Re: execution permission on procedures
    ... A user only need exec permission on the stored procedure in order to execute that procedure and having that do ... Tibor Karaszi, SQL Server MVP ... "Panos" wrote in message ...
    (microsoft.public.sqlserver.programming)