none
need to permissions to change / modify stored procedures and views within the database RRS feed

  • Question

  • how to grant the  permissions to change / modify stored procedures and views within the database   to a user .....

    thanks

    pradeep

    • Moved by Tom Phillips Thursday, October 17, 2013 3:18 PM Security question
    Thursday, October 17, 2013 3:00 PM

Answers

  • Sorry

    I was too quick

    there is not really an "ALTER OBJECT" or even "ALTER PROCEDURE" permission as it is

    What the BOL entry means, and is not clear by this quote, is, that one needs to have the "ALTER permission" on that specific "Object"

    But that's not the name of the permission

    It would look like this:

    GRANT ALTER ON Schema.ProcName. TO [UserName]
    GO

    unless you want to go through all objects one by one, you should look at the "CREATE PROCEDURE" from database-scope + "ALTER SCHEMA" permissions

    That then of course depends on your specific database-schema-layout and security requirements


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Thursday, October 17, 2013 3:39 PM

All replies

  • how to grant the  permissions to change / modify stored procedures and views within the database   to a user .....

    thanks

    pradeep

    Hello ,

    To change procedure you require ALTER PROCEDURE permission.

    http://technet.microsoft.com/en-us/library/ms345356.aspx

    To modify view  ALTER permission on OBJECT is required.

    http://technet.microsoft.com/en-us/library/ms173846.aspx

    Use above links to provide necessary rights


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, October 17, 2013 3:23 PM
  • Sorry

    I was too quick

    there is not really an "ALTER OBJECT" or even "ALTER PROCEDURE" permission as it is

    What the BOL entry means, and is not clear by this quote, is, that one needs to have the "ALTER permission" on that specific "Object"

    But that's not the name of the permission

    It would look like this:

    GRANT ALTER ON Schema.ProcName. TO [UserName]
    GO

    unless you want to go through all objects one by one, you should look at the "CREATE PROCEDURE" from database-scope + "ALTER SCHEMA" permissions

    That then of course depends on your specific database-schema-layout and security requirements


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Thursday, October 17, 2013 3:39 PM
  • Hi Shanky ,

    i am not asking how to modify and alter the views and sp's . how to grant the permissions to user ........

    pradeep

    Thursday, October 17, 2013 3:40 PM
  • Hello Pradeep,

    You have to grant alter permission to that stored proc to that particular user using below statement

    GRANT ALTER ON [SPname] to [Loginname] GO --Use this script to generate grant script for all procedures in a DB

    Use DBname GO select 'grant alter on [' + name + '] to [Loginname]' from sys.objects where type='P'

    --Change where clause to 'V' to generate grant script for View in that DB

    Executing the above script will generate the grant alter script. You can verify the script and execute it in the appropriate DB.

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    • Edited by udhayan Thursday, October 17, 2013 3:54 PM
    Thursday, October 17, 2013 3:54 PM
  • Hello Andreas ,

    Yes you are right the whole thing boil downs to alter permission,but I posted those lines from BOL which as you pointed out actually means alter permission .


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, October 17, 2013 5:47 PM