patternsqlMinor
Set permissions on a view
Viewed 0 times
permissionssetview
Problem
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.
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.
Solution
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.
Context
StackExchange Database Administrators Q#6083, answer score: 4
Revisions (0)
No revisions yet.