I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to update one of the tables by updating the view, but not the other. One table is live data from an important system and should not be changed by this application. I control the web application so it shouldn't ever generate a query that will update that table, but I don't want it to be able to at all.

Is there any way to set permissions so the view itself can only read from one of the underlying tables? Everything I know about permissions is at the user level, but I thought there was a way to make a view read-only.

  • I can't add comments yet but I just wanted to mention that you might also consider using the "WITH CHECK OPTION" when creating views that are editable. CHECK OPTION : Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed. msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx
    – SQLnbe
    Commented Oct 17, 2014 at 16:09

2 Answers 2


Don't grant UPDATE rights on the view. Grant update rights on specific column within the view. Only grant the update rights on the columns which you want the user / application to be able to update.


If you join two tables, you can update only one table at a time. So you should not touch your important tables.

Here is the link

Updatable Views

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

  • That works fine for queries I make, and SQL injection should be prevented on my set up, but unfortunately that property doesn't allow me to specify which table is "untouchable." Very good to know though.
    – Ben Brocka
    Commented Sep 22, 2011 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.