How to disable record delete by login

Posted on 2005-04-21
Last Modified: 2010-03-19
I am sure there is an easy way to do this but I can not find it.

I need to disable the delete record capabilty for specific users in a single table.  Delete needs to remain intact for other tables in the database.  I have tried the obvious of changing the permissions for the user on the table by checking the boxes for SELECT, INSERT and UPDATE only.  DELETE is left unchecked.  It seems that this user can still DELETE records from this table.

In looking at the permissions, it seems that for every table none of the options (select, insert, update, delete, exec and dri) are checked.  Am I missing something in my SQL Server setup?

Let me know if there is additional information needed to answer this question.
Question by:sjaeger949
    LVL 23

    Expert Comment

    You can try the Following

     - Write a Trigger
     - Detect the user name
     - if user name is 'x' dont let them delete roll back the tran.
    LVL 28

    Accepted Solution

    The user may belong to other groups that is allowed to DELETE from the table.  I suggest that instead of just leaving it blank or unchecked, select the 'X' in the check box for the DELETE.  This overrides his other permissions from other groups.
    LVL 1

    Author Comment

    I am an idiot.  Click one more time and it's an X.  DUH!  Thanks for the answer.  This worked perfectly.  Sorry for the newbie question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now