How to disable record delete by login

Posted on 2005-04-21
Medium Priority
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

by:Saqib Khan
ID: 13836250
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

rafrancisco earned 500 total points
ID: 13836261
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.

Author Comment

ID: 13836279
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 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