How to make a table READ ONLY

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.

Advertisements

2 thoughts on “How to make a table READ ONLY

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s