locked
how do I allow execute but deny insert/update/delete? RRS feed

  • Question

  • I'm attempting to create a readonly database role that still has access to execute all functions and stored procs (assuming those procs don't insert/update/delete).

    I created a role with grant on Execute and Deny on insert/update/delete.  This sucessfully allows for 90% of what I want but it is still allowing users to execute stored procs that contain insert/update/deletes.  

    Is there a simply way I can fix this without denying access to a stored procedures one by one?

    Wednesday, June 15, 2011 6:27 PM

Answers

  • No. That's the way it is supposed to work.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Mr. Wharty Thursday, June 16, 2011 3:06 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Wednesday, June 15, 2011 7:37 PM
  • You don't need to explicitly specify any DENY rights for insert/delete/update.  All you need is to specify is EXEC for stored procedures and scalar functions and SELECT on table-valued functions.

    Now, for you particular problem - you need to identify all stored procedures that modify data and exclude those procedures (grant execute to each specific procedure).  Or, you can grant execute on all, and explicitly deny execute on those that modify data.

    For example:

    GRANT EXECUTE ON schema::dbo TO role;

    DENY EXECUTE ON {stored procedure} TO role;  -- repeat for each procedure that modifies data

     


    Jeff Williams
    • Proposed as answer by Peja Tao Monday, June 20, 2011 7:50 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Sunday, June 19, 2011 4:45 PM
  • In addition,  you need to go throu all stored procedures that do I/D/U  and DENY execute perm on them

     

    DENY EXEC ON some_sp TO user


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Monday, June 20, 2011 7:49 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Monday, June 20, 2011 6:46 AM

All replies

  • No. That's the way it is supposed to work.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Mr. Wharty Thursday, June 16, 2011 3:06 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Wednesday, June 15, 2011 7:37 PM
  • You don't need to explicitly specify any DENY rights for insert/delete/update.  All you need is to specify is EXEC for stored procedures and scalar functions and SELECT on table-valued functions.

    Now, for you particular problem - you need to identify all stored procedures that modify data and exclude those procedures (grant execute to each specific procedure).  Or, you can grant execute on all, and explicitly deny execute on those that modify data.

    For example:

    GRANT EXECUTE ON schema::dbo TO role;

    DENY EXECUTE ON {stored procedure} TO role;  -- repeat for each procedure that modifies data

     


    Jeff Williams
    • Proposed as answer by Peja Tao Monday, June 20, 2011 7:50 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Sunday, June 19, 2011 4:45 PM
  • In addition,  you need to go throu all stored procedures that do I/D/U  and DENY execute perm on them

     

    DENY EXEC ON some_sp TO user


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Peja Tao Monday, June 20, 2011 7:49 AM
    • Marked as answer by Peja Tao Thursday, June 23, 2011 8:53 AM
    Monday, June 20, 2011 6:46 AM
  • In addition,  you need to go throu all stored procedures that do I/D/U  and DENY execute perm on them

     

    DENY EXEC ON some_sp TO user


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, June 20, 2011 6:46 AM