I'm having trouble creating a read-only view. I've got 1 or more tables that I wish to remain updatable but I want to create a view that covers the table and/or spans all the tables. However, I want the view to be select only. I can't seem to get it to work.
DENY
UPDATE ON [dbo].[MyView] TO [dbo] CASCADEAll that seems to execute my dbo user can still use...
Update
MyView set SomeID = SomeID + 10
Plus, ideally I just want to say, DENY UPDATE ON VIEW TO ALL
Any thoughts
Deny access to a view
Greg.Duffield
On that view object, in EM, try removing all permissions other than SELECT.
HTH
For more SQL tips, read my blog (below)
Stigmata
It's possible I'm doing something wrong but that doesn't seem to work. It seems if you have access to the underlying table you do pretty much anything via the view. I'm sure that can't be correct...can it
Ueslei
Ah interesting, I don't get that on SQL 2000 but I need any potential solution to work on 2005 too so that's interesting to know. I guess that makes sense, the code has been naughty in it's over-use of DBO so looks like it's time for a change. Thanks.
hjy
The problem is that you are trying to deny access to dbo. The dbo has all rights.
In 2005, when I try to deny access to dbo, I get the following message:
DENY SELECT on bobsSchema.test to DBO
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.