Creating readonly views on an Sql server

Question

According to MSDN, views made up of simple selections automatically allow you to use insert / update / delete statements in a table. Is there a way to prevent this: tell the Sql server that the view is read-only and you cannot use it to modify the table?

Answer 1

A better way would be to remove the UPDATE/DELETE/INSERT permissions in the view.

In addition, you can create an INSTEAD OF trigger on a view that simply does nothing to keep the updates silent, or there are several constructs that make the view not updatable . Thus, you can choose one that does not change the semantics or effectiveness, and then violates it.

Edit: Below, the score seems to match.

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

Answer 2

You can specify a UNION statement to cause SQL Server to crash during an INSERT / UPDATE / DELETE operation, for example:

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

The last query does not return any rows at all, but must have the same number of fields with the same data types as the first query in order to safely use UNION . See this link for more information: Various ways to make a table read only in a SQL Server database

Answer 3

The best way to handle this is to allow only access to views to be selected. Or Deny Insert / Update / Remove access for specified users. Works great. Also create "WITH (NOLOCK)" views.