Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer

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.
share|improve this answer
    
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 Sep 22 '11 at 20:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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