MODIFY  permission for ALL stored Procedures in the dbo schema

Posted on 2012-09-19
Last Modified: 2012-09-24

I'm pretty new on DBA functions in SQL server. I have a user who got  data read access to SQL DB instances, however, he need to access the stored procedures with modified
permission(to see the inside content of all stored procedures) on the same schema.
How do I achive this, what are the permission setting?

Thanks in Advance.
Question by:keplan
    LVL 37

    Expert Comment

    these are the available database level roles

    and these are the server roles

    you will have to manually grant permissions on the procedures you want the user to work with
    you either grant the view definition or the alter, depending on what the user needs to do
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Create Schema and then assign to user.

    Give grant permission for Create procedure to that user.
    LVL 68

    Accepted Solution

    GRANT VIEW DEFINITION ON OBJECT::[stored_proc_name] TO user_name

    For all stored procs, run the code below to gen the statements, then run the gen'd statements themselves:

        'GRANT VIEW DEFINITION ON OBJECT::[' + name  + '] TO user_name'
        type = 'P'

    Author Comment

    Hi All,

    All the answers are work around, I just want to know in a simple command to grant
    EXECUTE permission to all the User Stored procedures in a Database.
    Basically, The user need the edit(Modify) permission to see what are the script in  a Stored procedures.

    Thanks for all again.
    LVL 68

    Expert Comment


    Do they just need to EXECUTE or do they need to ALTER procedures?

    If you want someone to be able to EXECUTE anything in the database you can do this:

    GRANT EXECUTE TO [user_name]

    But if they need to be able to ALTER *only* procedures, that's trickier.  There's no easy, direct command to give that specific permission.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
    Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
    Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now