locked
user update permission with where clause RRS feed

  • Question

  • Hi,

    if user having update / delete permission they can update / delete table. But problem is that some time user update/delete with out where clause and for that whole table affect. 

    Please let me know is there any process is there where we can restrict user to use where clause in update/delete command

    Thursday, June 21, 2012 10:16 AM

Answers

  • There is no such direct functionality. You can possibly have a trigget whcih checks number of rows affected and then conditionally does RAISERROR and ROLLBACK.

    I would question why users are allowed to direct-modify the data in the first place, if these things occurs.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Shulei Chen Monday, June 25, 2012 9:51 AM
    • Marked as answer by amber zhang Monday, July 2, 2012 5:46 AM
    Thursday, June 21, 2012 11:37 AM
  • Educate the user to have WHERE condition.

    BEGIN TRAN

    Update....

    ---Opps see 50,000 rows are affected

    ROLLBACK TRAN 

    Instead of granting permissions on underlying tables, grant permission to stored procedure that does an update withing based on parameters.



    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Shulei Chen Monday, June 25, 2012 9:51 AM
    • Marked as answer by amber zhang Monday, July 2, 2012 5:46 AM
    Thursday, June 21, 2012 11:39 AM

All replies

  • There is no such direct functionality. You can possibly have a trigget whcih checks number of rows affected and then conditionally does RAISERROR and ROLLBACK.

    I would question why users are allowed to direct-modify the data in the first place, if these things occurs.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Shulei Chen Monday, June 25, 2012 9:51 AM
    • Marked as answer by amber zhang Monday, July 2, 2012 5:46 AM
    Thursday, June 21, 2012 11:37 AM
  • Educate the user to have WHERE condition.

    BEGIN TRAN

    Update....

    ---Opps see 50,000 rows are affected

    ROLLBACK TRAN 

    Instead of granting permissions on underlying tables, grant permission to stored procedure that does an update withing based on parameters.



    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Shulei Chen Monday, June 25, 2012 9:51 AM
    • Marked as answer by amber zhang Monday, July 2, 2012 5:46 AM
    Thursday, June 21, 2012 11:39 AM
  • Thanks guys.
    Thursday, June 21, 2012 2:21 PM