0

I am using SQL Server 2008 R2. I want to make a column data undeletable. Is it possible to make the data undeletable without using Triggers? If it is, please let me know.

Aniruddha Nath
  • 11
  • 7
  • 2
    No; There will always be someone who can delete that data. There will always be Someone who can DROP the tables... The system admin login can do anything... But, you can `REVOKE DELETE ON thatTable FROM aSpecificUser;` In short, you need to control this with User Permissions. – MatBailie May 22 '18 at 12:41
  • I think you need to provide more information - the definition of the table, and also what you would want to happen to the rest of the row (or do you mean that the whole row should be undeletable if one specific column has a particular value?) This feels to me like something best achieved within your application code, or is that not a feasible option? – MandyShaw May 22 '18 at 12:41
  • I am also wondering whether a constraint might help you with this. – MandyShaw May 22 '18 at 12:44

1 Answers1

5

You can GRANT/REVOKEon column level.

REVOKE UPDATE ON elbat (nmuloc) FROM <role/user>;

Like this the respective role/user cannot update nmuloc in elbat anymore. (And set it to NULL which I assume is what you mean by deleting "column data".)

sticky bit
  • 36,626
  • 12
  • 31
  • 42