June 19, 2002 at 3:20 am
Does anyone know of a way to grant a user or role permission to Create and Modify Views without having to grant the same rigths to Tables?
Thanks, Jane
June 19, 2002 at 4:37 am
HI, from bol:
To create a view, the user must have CREATE VIEW permission along with SELECT permission on the tables, views, and table-valued functions being referenced in the view, and EXECUTE permission on the scalar-valued functions being invoked in the view.
In addition, to create a view WITH SCHEMABINDING, the user must have REFERENCES permissions on each table, view, and user-defined function that is referenced
June 19, 2002 at 4:46 am
Thanks! Actually creating views is not so much a problem as altering them. It seems that only people with dbo access can alter views, and then they can alter tables as well. I tried revoking their rights to alter talbes but got an error message: "Privilege ALTER TABLE may not be granted or revoked"
Any thoughts gratefully received.
Jane
June 19, 2002 at 4:59 am
HI, according to bol for altering views the same permissions as the create view aply. You can give a user ( or a role) create view rights with:
grant create view to myuser
alter view rights with:
grant alter view to myuser
June 19, 2002 at 5:34 am
Perhaps I am missing something!! but the command
grant create view to highleveluser
works fine whereas
grant alter view to highleveluser
gives the message: "Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'view'."
Have you actually tried it and got it to work on your database?
Thanks again
Jane
June 19, 2002 at 7:21 am
Hi, you where right i had asumed it worked for alter view as well. My excusses. If you assign create view rights to a user then he can alter the view he has created but he can not alter the view of other users. Sorry that i can not be of more help. Best regards,
Klaas-Jan
June 19, 2002 at 8:53 am
Yes, this can be done regardless of what BOL says. You can trick the system because it only check the permissions of what the view is selecting, not the actual table. So what you do to fix the issue is create a view of a view like so:
Example Table is Table1
Example User is User1
create view dbo.Table1_view as select * from dbo.Table1
create view User1.Table1 as select * from dbo.Table1_view
grant select on dbo.Table1_view to User1
grant select on User1.Table1 to User1
grant create view to User1
-- notice there is no grant to dbo.Table1
When User1 logs in he\she can perform the following without error:
select * from Table1 -- this selects the User1.Table1 view not dbo.Table1
create view MyTable1 as select * from Table1 -- same as above
alter view MyTable1 as select Column1 from Table1 -- notice alter works now too
The user is unable to perform this command:
select * from dbo.Table1
Hope this helps,
DanW
June 19, 2002 at 5:27 pm
Backup, you state the problem is they cannot alter the view. Have they tried
ALTER VIEW theirlogin.viewname
If not a memeber of dbo then the view ill be created as theirlogin.viewname and to ALTER may need to qualify (altough I have not seen this happen). And only they or a dbo can do anything to it.
From SQL BOL
quote:
PermissionsALTER VIEW permissions default to members of the db_owner and db_ddladmin fixed database roles, and to the view owner. These permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with SELECT permission on the tables, views, and table-valued functions being referenced in the view, and EXECUTE permission on the scalar-valued functions being invoked in the view.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/19/2002 5:30:01 PM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply