Post reply

Question

  • February 1, 2019 at 9:23 am

    #370394

    User ran delete statement and cause the prod issue. Looking to revoke user permissions from the prod. However, user still wants update permission. I was thinking going forward the future script changes has to run by DBA. Is any other solutions you can advise to accomodate update permissions?

  • February 1, 2019 at 9:32 am

    #2020876

    Grant the user UPDATE permission on the tables in question, and deny DELETE on the same tables.  You can do that directly or via membership of a role that you assign those permissions.  Make sure that the user doesn't get DELETE permission through membership of a different role, such as db_datawriter.

    John

  • February 1, 2019 at 9:44 am

    #2020879

    I believe that the DENY permissions take precedence over any GRANT permissions.

    Also, OP, when you are creating topics, it's very helpful to summarize the question in the name of the thread.  "Revoke DELETE permissions" is much more descriptive than "Question".

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 1, 2019 at 10:07 am

    #2020882

    I would seriously question the need for this user to update data directly in production. If this user already caused an issue by deleting, I would expect the same thing happening with and UPDATE without WHERE.
    I'll say that you should revoke all permissions in prod and every change should be reviewed and documented before applied.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • February 1, 2019 at 10:35 am

    #2020886

    Absolutely agree with Luis.

  • February 1, 2019 at 10:42 am

    #2020887

    An update can be just as bad as a delete statement(potentially worse since a mass delete is likely to be immediately noticed a mass update might screw things up for far longer), if the user is not responsible enough for delete permissions they are likely also not responsible enough for update permissions.

  • February 1, 2019 at 11:26 am

    #2020891

    Luis Cazares - Friday, February 1, 2019 10:07 AM

    I would seriously question the need for this user to update data directly in production. If this user already caused an issue by deleting, I would expect the same thing happening with and UPDATE without WHERE.
    I'll say that you should revoke all permissions in prod and every change should be reviewed and documented before applied.

    +100

    ...

  • February 1, 2019 at 1:30 pm

    #2020902

    Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply