Creating readonly views in Sql Server

Question

According to MSDN, views composed of simple selects automatically allow you to use insert/update/delete statements on the table. Is there a way to prevent this - to tell Sql Server that the view is readonly, and you can't use it to modify the table?

Answer 1

The best way would be to remove UPDATE/DELETE/INSERT permissions on the View.

Apart from that you could create an INSTEAD OF trigger on the view that simply does nothing to have the updates silently fail or there are quite a few constructs that make views non updatable. So you can pick one that doesn't change semantics or efficiency and then violate it.

Edit: The below seems to fit the bill.

CREATE VIEW Bar
AS
SELECT TOP 100 PERCENT x
FROM foo
WITH CHECK OPTION

Answer 2

You could specify an UNION operator in order to make SQL Server fail during the INSERT/UPDATE/DELETE operation, like this:

create view SampleView
as
  select ID, value from table
  union all
  select 0, '0' where 1=0

The last query doesn't return any rows at all, but must have the same amount of fields with the same data types as the first query, in order to use the UNION safely. See this link for more info: Different ways to make a table read only in a SQL Server database