[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MODIFY  permission for ALL stored Procedures in the dbo schema

Posted on 2012-09-19
Medium Priority
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

ID: 38417323
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
ID: 38417403
Create Schema and then assign to user.

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

Accepted Solution

Scott Pletcher earned 675 total points
ID: 38418223
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

ID: 38430773
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 70

Expert Comment

by:Scott Pletcher
ID: 38430862

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

873 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