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?

Solution 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

Solution 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

Comments

[ZygD]

+1 permissions should prevent this not code. But if folk can write via the view then I suspect there are permissions on the base tables too...

[Idan Arye]

Thanks, works like a charm. Those views will actually refer to another database, which the user won't have access to. I want to make them readonly so the user won't be able to modify that database he doesn't have access to via the views in the database he does have access to. I want to use per-database permissions instead of per-object permissions to keep things simple and to be more sure that I don't miss a breach in the permission definitions.

[BIBD]

I'll point out a danger with the code appended to the end. It seems to hinge on the 'WITH CHECK OPTION'. If you come back and late edit the View with SQL Server Management Studio, it won't include the 'WITH CHECK OPTION' and the view will be read/write again.

[Christoph]

You can also add a "UNION SELECT TOP 0 * FROM table" (at least in SQLServer 2014).

[Ben]

For anyone else looking for clarity, SELECT TOP 100 PERCENT needs to be combined with WITH CHECK OPTION at the end of the view in order for write attempts on this view to fail. In my testing with 2014, only having one of those still allows the view to be updated.

[m81]

For anyone else researching this, it doesn't seem to present a consistent behavior for me. I created my view with both "SELECT TOP 100 PERCENT" and "WITH CHECK OPTION", yet if I attempt to delete data from the view, the deletes affect the underlying table. This is on SQL Server 2016.