Visakh Murukesh, SQL Server MVP and my friend, posted about SQLServer Tips: How To Make a Table Read Only in Database in which he showed many methods on how to make a table read-only
I know another method of doing it using a view.
Let us create the following dataset
create table sales_target(sales_year int, sales_target decimal(12,2))
insert into sales_target (sales_year,sales_target)
select 2015, 2000000 union all
select 2016, 2500000
Now create an view out of this table which has union/union all included as shown below
CREATE view sales_target_read_only as
select * from sales_target
union all
select * from sales_target where 1=0
Now let the user access to the view sales_target_read_only and not the underlying table sales_target.
If you try to add data to this view
insert into sales_target_read_only(sales_year,sales_target)
select 2017, 3000000
You get the following error
Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.
Similar error occurs when try to update this view
update sales_target_read_only set sales_target=10000 where sales_year=2015
The error is
Msg 4406, Level 16, State 1, Line 2
Update or insert of view or function 'sales_target_read_only' failed because it contains a derived or constant field.
If you try to delete rows from this view
delete from sales_target_read_only where sales_year=2015
You get this error
Msg 4426, Level 16, State 1, Line 1
View 'sales_target_read_only' is not updatable because the definition contains a UNION operator.