locked
Create View permissions RRS feed

  • Question

  • Hi Everyone,

    I hope you can help me.  I want to give a particular database user permission to create new views and then be able to subsequently alter those views.  However, I wish to restrict them from being able to modify any views that have been or will be created by any other users.  I hope this makes sense.  Any help will be very gratefully recieved :-)

     

    Tuesday, November 2, 2010 3:45 PM

Answers

  • The CREATE VIEW discussion in SQL Server Books Online says:  "Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created."

    If everybody is creating views in the "dbo" schema, then there is no way to keep one user from changing some other user's view.   Check the ALTER SCHEMA permission as described here:

    http://msdn.microsoft.com/en-us/library/ms187940.aspx

    Of course, if each user is given a different schema, then you will have more control over their rights.  Then you will need to manage the extra schemas as well, but that may fit your model.

    RLF

    Tuesday, November 2, 2010 5:40 PM
  • Please always tell us what version you are using. 

    In SQL 2000, the user who creates the view will own the view, and be able to modify it, but other users (other than dbo or sa) will not have any rights to this user's views.

    In 2005+, the user needs create view permission plus ALTER SCHEMA permission on the schema where the view will be created. If there are other users with ALTER SCHEMA permission on the same schema, they WILL be able to modify this view.

    If you tell us your version, and more specific information about your needs, we can provide more specific answers.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, November 2, 2010 5:56 PM

All replies

  • The CREATE VIEW discussion in SQL Server Books Online says:  "Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created."

    If everybody is creating views in the "dbo" schema, then there is no way to keep one user from changing some other user's view.   Check the ALTER SCHEMA permission as described here:

    http://msdn.microsoft.com/en-us/library/ms187940.aspx

    Of course, if each user is given a different schema, then you will have more control over their rights.  Then you will need to manage the extra schemas as well, but that may fit your model.

    RLF

    Tuesday, November 2, 2010 5:40 PM
  • Please always tell us what version you are using. 

    In SQL 2000, the user who creates the view will own the view, and be able to modify it, but other users (other than dbo or sa) will not have any rights to this user's views.

    In 2005+, the user needs create view permission plus ALTER SCHEMA permission on the schema where the view will be created. If there are other users with ALTER SCHEMA permission on the same schema, they WILL be able to modify this view.

    If you tell us your version, and more specific information about your needs, we can provide more specific answers.


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, November 2, 2010 5:56 PM