Make tables in MS SQL Server available for insertions only?

Question

I am trying to find out if there are mechanisms that will allow for data to only be inserted into tables and not deleted or updated in SQL Server?

Any help would be great, doing research at the moment and have become stuck.

Yes: Make sure the user executing the queries only has INSERT permissions on the table. — Alex K., Jul 10 '17 at 13:43
Cheers I had thought of this but wasn't sure if I could do it! — HelloWoRlD, Jul 10 '17 at 13:48
have a look here: https://stackoverflow.com/questions/8684979/protect-row-from-deletion-in-mysql — Esteban P., Jul 10 '17 at 13:53

score 1 · Answer 1 · answered Jul 10 '17 at 14:16

You can grant and revoke privileges in the tables:

    # only permission for insert and select
    GRANT SELECT, INSERT ON employees TO public;

    # or revoke other permissions
    REVOKE UPDATE,DELETE  ON mytable FROM public;

You can control permissions for user(s)/public

score 0 · Answer 2 · answered Jul 10 '17 at 13:44

0

Presumably, you mean insertions and selects.

If so, you can create instead of triggers on the table for deletes and updates. These triggers would simply return errors or warnings and not change the table.

answered Jul 10 '17 at 13:44

Gordon Linoff

1,242,037
58
646
786

Yes, insertions and selects they are all I want on certain tables. Thanks I'll do some reading on these! – HelloWoRlD Jul 10 '17 at 13:49

score 0 · Answer 3 · answered Jul 10 '17 at 14:10

0

I personally wouldn't create trigger on each table to prevent INSERT and just disallow UPDATE and DELETE rights for all users.

answered Jul 10 '17 at 14:10

Evaldas Buinauskas

13,739
11
55
107

Perfect it seems like the more secure option, thanks – HelloWoRlD Jul 10 '17 at 14:13
This should be also more manageable. Not saying triggers would not work, but generally it's recommended to avoid them and use as a last resort. – Evaldas Buinauskas Jul 10 '17 at 14:15

Make tables in MS SQL Server available for insertions only?

3 Answers3