Friday, March 12, 2010

How to make Read-only VIEWS in SQL-Server

We all know that Views in SQL Server are editable. Unlike Oracle, there is no direct option available to make a view read-only (as per my knowledge, correct me if I am wrong). So i was just looking into some options to make Views read-only and users should not be able to update the views.

One solution to this is, only assign SELECT permission on view to the specified users/role. With that said user/role will only be able to SELECT from the view.

But if you dont want to mess around with security and dont want anyone to update views at all then there is one "cheat" solution. While creating the View use Derived fields even if you can directly get the fields from table.

Following is one VIEW that I created where I have two derived columns and one normal column mapped from table.

CREATE VIEW [dbo].[TestReadOnlyView]
AS
SELECT case when PriorityTypeID IS NULL THEN NULL
ELSE PriorityTypeID end as PriorityTypeID
,case when PriorityTypeDesc IS NULL THEN NULL
ELSE PriorityTypeDesc end as PriorityTypeDesc
,SortOrder
FROM dbo.PriorityTypes


Now with this VIEW, you can update SortOrder column but you wont be able to update other two columns.
Try out yourself.

I am still looking into this, so if anyone is having any other solution or opinion then do share with me.