Re: Granting EXEC for read only SPs

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: Granting EXEC for read only SPs
    ... clearly understanding which stored procedures the user should have rights to ... problem is that the stored procedure is running under its own rights ... I created database role 'X' and granted EXEC permission for all SPs. ... the ones that modify data. ...
    (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: 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: Question on Database Project
    ... you don't have rights to access the SPs or you created the SPs in ... the wrong database. ... This posting is provided "AS IS" with no warranties, and confers no rights. ... I get the error - "Stored procedure not ...
    (microsoft.public.dotnet.framework.adonet)