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.
keplanAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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:


SELECT
    'GRANT VIEW DEFINITION ON OBJECT::[' + name  + '] TO user_name'
FROM
    sys.objects
WHERE
    type = 'P'
ORDER BY
    1
0
 
momi_sabagCommented:
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
0
 
Alpesh PatelAssistant ConsultantCommented:
Create Schema and then assign to user.

Give grant permission for Create procedure to that user.
0
 
keplanAuthor Commented:
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.
0
 
Scott PletcherSenior DBACommented:
??

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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.