views not updateable

[othellomy]

How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.

[othellomy]

On Mar 23, 4:06 pm, [e-mail] wrote:

How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.

I have tried:
revoke delete,insert,update on v_t1 to dbo

bit its not working...

[othellomy]

On Mar 23, 4:13 pm, [e-mail] wrote:

On Mar 23, 4:06 pm, [e-mail] wrote:

How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.

I have tried:
revoke delete,insert,update on v_t1 to dbo

bit its not working...

Okay found a solution. If I introduce an aggregate function then it
works. So I ended up aggregating all the columns from the select list.
So instead of saying:
Create view view1 as
Select column1, column2

>From Base_table

Not Updateable:
Create view view1 as
Select column1, column2

>From Base_table

Group by column1,column2

I wish there is an easier way than this.

[Damien]

On Mar 23, 10:34 am, [e-mail] wrote:

On Mar 23, 4:13 pm, [e-mail] wrote:

On Mar 23, 4:06 pm, [e-mail] wrote:

How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.

I have tried:
revoke delete,insert,update on v_t1 to dbo

bit its not working...

Okay found a solution. If I introduce an aggregate function then it
works. So I ended up aggregating all the columns from the select list.
So instead of saying:
Create view view1 as
Select column1, column2

From Base_table

Not Updateable:
Create view view1 as
Select column1, column2>From Base_table

Group by column1,column2

I wish there is an easier way than this.

create trigger T_NoUpdates on view1 INSTEAD OF INSERT,UPDATE,DELETE AS
RAISERROR('No updates allowed',16,1)

Damien

[Hugo Kornelis]

On 23 Mar 2007 03:13:01 -0700, ot*******@yahoo.com wrote:

>On Mar 23, 4:06 pm, [e-mail] wrote:

>How do I make a view non updateable? I want to create a view so that I
will not be able to update, insert or delete the view so that base
table is not affected (except dropping the view itself). Thanks.

I have tried:
revoke delete,insert,update on v_t1 to dbo

bit its not working...

Hi othellomy,

The REVOKE keyword is to revoke a permission that was earlier GRANTed
explicitly.

To explicitly deny access to an object that would by default allow
access, you use DENY:

DENY DELETE, INSERT, UPDATE ON v_t1 TO ALL

However, this will not affect any user in the db_owner database role or
any user in the sysadmin server role, since security checks are bypassed
for these users.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis