Create View Only

Good afternoon,

I have a user that needs read only access to all tables in a particular database - Which is working fine.

He also needs to create views, as well as the above, but nothing else.

Is this possible?

Many thanks.

This can be done, but there are a couple of caveats; hang on. Look at this example:

Code Snippet

create view dbo.v_doWhat
as
select what from doWhat
go

/*

Server: Msg 2760, Level 16, State 1, Procedure v_doWhat, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

*/

alter view kawTest.v_doWhat
as
select what from doWhat

go

delete from v_doWhat

/*
Server: Msg 229, Level 14, State 5, Line 1
DELETE permission denied on object 'doWhat', database 'kawTest', owner 'dbo'.
*/

Note in the first create that because you do not have DBO privilege that you cannot create DBO views. Secondly, the views that you create will be for a specific schema and in SQL 2000 they will be owned by a specific user.

I am probably leaving out more; hopefully, somebody will pick me up.

Another thing to think about: If the developer or user has permission to create views, should they also have the ability to create functions?