SQL 2005 - user rights

[ecka00]

I need to grant a user to view all stored procedures in a database. Is there a server role other than sysadmin that grants said permissions? If not, does anyone have a script that will give a resultset of all SP's in a database so i can alter the scripts output to grant view to a specific user?

[rmiao]

What do you mean view? User can list sp names? Can execute them or just look at code?

[skhanal]

You can grant EXECUTE permission on the schema that owns stored procedure to the user.

[ecka00]

view the code.

[rmiao]

Need view definition permission on sps.

[ecka00]

i know i can grant said permissions on individual sps however, i want to grant a user view permissions for all sps and there are a lot of them. is there a script to grab all sps' in a database so i can script something out and give a user view to all sps?

[skhanal]

select name from sys.procedures

[rmiao]

Try following, it'll generate grant statements you need. Replace user with user name or db role:

SELECT 'grant view definition on ' + name + ' to user' FROM sys.procedures

[ecka00]

that's exactly what i was looking for. as always---thanks for the help!