asked on

MODIFY permission for ALL stored Procedures in the dbo schema

Hi,

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.

momi_sabag

these are the available database level roles
http://msdn.microsoft.com/en-us/library/ms189121.aspx

and these are the server roles
http://msdn.microsoft.com/en-us/library/ms188659.aspx

you will have to manually grant permissions on the procedures you want the user to work with
http://msdn.microsoft.com/en-us/library/ms191291.aspx
you either grant the view definition or the alter, depending on what the user needs to do

Alpesh Patel

Create Schema and then assign to user.

Give grant permission for Create procedure to that user.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keplan

ASKER

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.

Scott Pletcher

??

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.