Re: Granting EXEC for read only SPs

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: Unable to prevent OU deletion by Domain Admins?
    ... This posting is provided "AS IS" with no warranties, and confers no rights. ... >>>> It is even worse when Microsoft's own guidelines for parsing ACLs ... >>>> that DENY ACLs trump any allow ACLs ... >>> the list of permission entries in the DACL. ...
    (microsoft.public.win2000.active_directory)
  • Adding Roles With Selected Permission In Custom Add Role ASpx Page
    ... I have made a custom aspx form that lists down all the available permission ... rights for all groups in a Grid, and provides a text field for Site Group ... document libraries, edit Web discussion comments in documents, and customize ...
    (microsoft.public.sharepoint.portalserver.development)
  • RE: Need help understanding file rights
    ... > I'm having some trouble understanding how Windows server does file rights. ... > to their home directories only and still be able to backup the server. ... Let's make it short, you can set permission ...
    (microsoft.public.windows.server.general)
  • Thinking outside the box on file systems
    ... contains file data, name data, and permission data. ... files that you have some rights to and files where you ... The ACLs that were added to Linux were a step in the ... there would be mid level roles where users and objects ...
    (Linux-Kernel)
  • Re: Stored procedure to drop and create table for end user
    ... > having permission on the database that they should not (like delete rows and ... > such) so the only thing the end user has access to is execute permission on ... > back with select permission not granted on a table the stored procedure is ... > tables (which i dont like doing considering some of them have sensative data ...
    (microsoft.public.sqlserver.programming)