Preventing record deletion 1

[vbajock]

I have a table that I do not want any records deleted from unless I myself delete the record manually. Is there anyway to set a property on this table in SQL Server so that records can only be added or edited but not deleted by any programmer or user who accesses the table via ODBC/OLE Db? The table is currently in a SQL 2000 db.

 

[SQLBill]

Yes you can. Here is the syntax for it taken from the SS2000 BOL.

DENY SELECT, INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Another option to consider is don't deny the permissions. Instead create a trigger that before delete it records the person's login and what they are trying to delete, and then ignore the delete. With this method you have a copy of who's trying deletions and what they are trying to delete.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[vbajock]

If I use the DENY method in your first post, if I want to deny it to all users, can I use: TO * ?

 

[SQLBill]

I don't believe that would work, nor would you want it to. SA still needs the right to do anything and you probably want to be able to delete when you need to do so.

However, you might try creating a test table and running that command with TO ALL.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875