Post reply

Permission to Create, Modify & Delete Views only.

  • June 27, 2012 at 10:08 am

    #258596

    Hello,

    An end user has requested for Create, Modify and Delete "View" permissions on a SQL 2008R2 Server database. I do not want to give the user any more permissions than what he has asked for therefore could you please help and tell me how this could be achieved?

    Any help will be highly appreciated.

    Thanks

    Kailash.

  • June 27, 2012 at 10:50 am

    #1506269

    As far As i know, SQL Server doesn't have the specific granularity to restrict creation to only views;

    giving someone ALTER permissions(like via GRANT ALTER ON schema or the role ddl_admin) lets them build procs ,functions and tables,as well as views.

    In a very simlar thread, I offered a suggestion to do that witha DDL trigger, where the DDL trigger only allows CREATE/ALTER/DROP of views, and all other DDL get rolled back in the DDL trigger.

    take a look at this thread and see if that can help you, where we create the role "ViewMaker",and restrict it with the DDL trigger:

    http://www.sqlservercentral.com/Forums/Topic1241211-391-1.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 27, 2012 at 10:53 am

    #1506270

    AFAIK, Lowell is correct. You can't restrict this to just views.

  • June 27, 2012 at 11:05 am

    #1506282

    Create view requires Alter permission on the schema. With alter on the schema, the user would then be able to alter other objects within that schema.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 28, 2012 at 2:35 am

    #1506491

    Thank you all (Lowell, Steve Jones & Jason) for all your help and advice. I will work with Lowell's DDLTrigger example and customise it to suit my needs.

    Lowell, Thank you to you for writting the DDLTrigger example and taking time to explain it to us.

  • June 28, 2012 at 2:59 am

    #1506497

    Just a thought, Can I not do the following?

    1) Provide only CREATE VIEW permissions on the DATABASE (Database Permissions)

    2) Create a new Schema and give Alter permissions to this new Schema

    Would the above not work, As the user will only be able to create views and within the new schema?

  • June 28, 2012 at 9:49 am

    #1506709

    Yes, you can create a new schema and grant the user the permissions there. That would limit what the user can do to just that schema.

    Create new schema first. Grant create view within that schema.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply