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
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com