I ran this from one session:

CREATE TABLE [dbo].[tbltest] (

[Pkey] [int] IDENTITY (1, 1) NOT NULL ,

[fname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

go

CREATE VIEW dbo.vwTest

AS

SELECT fname, lname

FROM dbo.tbltest

go

grant select on tbltest to public

go

grant insert, update on vwtest to steve

go

drop view vwtest

go

drop table tbltest

go

(except for the drops) and from another session, logged in as "steve", I ran:

select * from tbltest

which worked and then

select * from vwtest

which didn't. As expected. This worked.

insert vwtest select 'steve', 'jones'

but this did not, I get a "select permissions denied"

update vwtest set fname = 's' where fname = 'steve'

however

update vwtest set fname = 's'

works. Apparently the WHERE clause needs select access.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/

http://www.dkranch.net