Post reply

Giving right to CREATE, ALTER and DROP View without being DDLAdmin

  • March 14, 2011 at 7:43 am

    #246741

    Hi I want to give the following permissions to SQL Login (stk_prd) for a database without giving the ddladmin right to this SQL Login :

    For a create view : There is no problem, that's fine

    GRANT CREATE VIEW TO [stk_prd]

    GO

    BUT

    For an alter or drop view, there is no option to done it (a GRANT ALTER, DROP or UPDATE view doesn't exist) !

    So if you have the CREATE right, does the ALTER and DROP permissions were implicit ?

    BOL isn't implicit on this question 😉

    If no, which rights are necessary to alter or drop the view ?

    Regards

    Philippe

  • March 14, 2011 at 8:13 am

    #1298339

    i believe if the login CREATED a view, by default, it is it's owner, and thus can alter or drop the objects it creates.

    if you want the login to be able to fiddle with views/objects it didnot create, i think you have to GRANT ALTER TO loginname ON OBJECTNAME.

    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!

  • March 14, 2011 at 8:57 am

    #1298375

    Yes lowell, I guess, by default, it can actually change or delete the objects it creates, but MS should have been more explicit on the subject. It is actually possible to make a GRANT ALTER TO but it requires to spend some time with each new objects created ...! And with over 100 SQL instance to survey, having time isn't easy :w00t:

  • March 14, 2011 at 9:04 am

    #1298382

    Philippe

    Have you tried a DDL trigger? You should be able to automatically grant permissions on an object as soon as it's created. I've never tried using a trigger for that, but I don't see why it shouldn't be possible.

    John

  • March 16, 2011 at 6:50 am

    #1299318

    Good suggestions 🙂 When I will have time, I'd try to done it.

    Regards

Viewing 5 posts - 1 through 4 (of 4 total)

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