How to make SQL Server View Read Only?

In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (even you can INSERT/UPDATE/DELETE data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users

See the below example how we can make the view read only.

--creating a sample table
Create table tbl1
	myID int,
	name varchar(10)

--inserting data
insert into tbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--creating sample view
create view vwtbl1
select * from tbl1

--inserting data using view
insert into vwtbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--altering view to make it readOnly
alter view vwtbl1
select myid,name from tbl1
union all
select 0,0 where 1 =0

INSERT/UPDATE/DELETE will fail with the below errors.

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'vwtbl1' failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1
View 'vwtbl1' is not updatable because the definition contains a UNION operator.

1 thought on “How to make SQL Server View Read Only?

  1. Pail Writer

    Thanks so much for this helpful yet simple approach to switching a view to “read only” mode.
    However, I did notice one error in your code.
    In the “ALTER VIEW” statement, after the “UNION ALL” the last line – “SELECT 0, 0 WHERE 1=0”
    should read “SELECT 0, ‘%’ WHERE 1=0”
    Otherwise it throws an error while trying to convert the “name” string to an “INT” value.
    Just an FYI.


Thanks for the comment, will get back to you soon... Jugal Shah

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s