December 4, 2002 at 11:03 am
I have a user that has created some views and I would like to now give another user permissions to make changes to these views. The views were created in Enterprise Manger. I am running SQL 2000.
The second user can create thier own views on the database, so the permissions are setup correctly. The 2nd user can go into the design of the first users view, however, when they make changes and try to save the chnages to the view the following error message comes up.
ODBC error [Microsoft][ODBC SQL Server Driver][SQL Server} User does not have permissions to perform this operation on view 'owner.object'.
I have given the 2nd user update permissions on the view. Does anyone know how to properly setup the permissions to allow a non owner of a view to make changes.
Thanks,
Tim Olig
IT Manager
Mid-States Aluminum Corp.
SQL 2000
December 4, 2002 at 11:17 am
The easy way is to make the 2nd user a member of the role db_ddladmin. This will allow them to make any DDL change so it might not be what you want.
If you don't want to do that, set everyone up with access to a common owner and have them create all objects under that ID. Then they'll have access by default.
December 4, 2002 at 12:50 pm
What you're most likely experiencing is called a broken ownership chain. To prevent this, objects in a chain should be owned by the same user (e.g. table and view should be owned by same person). The permission you gave the second user, update on the first user's view, allows the second user to update records in the view, not modify the view definition.
Lori
December 4, 2002 at 3:27 pm
I gave the user the db_ddladmin role and it now works. However, this also gave this user rights I do not want them to have.
Lori responded indicating I have a broken chain of ownership. The owner of the views has always been different than the owner of the table. I have done it this way by giving some users CREATE VIEW permissions on the table they want to create views on. I think what I am finding is that there is no way to give a 2nd user the ability to modify another persons view.
If I cannot do this, what is the easiest way to change owners of a view?
In case it matters, I am using NT authentication for security, not SQL logins.
Tim
December 4, 2002 at 3:42 pm
sp_changeobjectowner can be used to change the owner.
Lori
December 4, 2002 at 4:50 pm
Easiest thing to do is change owenrship to DBO that way will match the table. Of course this means the user who created will not be able to change it at that point.
I would suggest thou, create a role instead and put the users in the role together then grant design view create to the role, both then should be able to affect each other. See GRANT in SQL BOL.
December 9, 2002 at 1:42 pm
I have changed the owner of the views and I also created a new role and put both users in that role. Each user can change any views that is owned by the Role, however, when one of them creates a view, they become the owner of the view and the other user cannot change the view. If I change the owever to the Role, then both users can change the view. Is there a way to have owner of the view be the Role rather than the individual user? What I am simply trying to do is give two users the ability to change each others views. Is this something that cannot be done or should not be done?
I appreciate the help thus far, it has helped alot. I just need a little more help, or have someone tell me that I am trying to do something that cannot be done.
Thanks,
Tim
December 9, 2002 at 5:50 pm
It can be done my referencing tha name of the role instead.
Ex. I created a role vowner and put a user measme in it.
When I do
CREATE VIEW vw_test1 AS
SELECT .......
it is saved as measme.vw_test1 and only I have access to it.
Since the users do not have rights to create objects as dbo but their role was granted access to create view. Reference the twopart name (owner.object) with the role.
Ex
CREATE VIEW vowner.vw_test1 AS
SELECT .......
Now since both are a member of vowner they will be able to access the objects owned by it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy