If you need to grant permissions to a login that only needs to view stored procedures (contents not just names of SP's). Further you may not want to grant that login with DB_OWNER or DB_DDLADMIN roles within that database, so how to achieve this task? As usual if that login needs the execute permission you have to "grant execute" permission on sp(s) you want login to execute, also ensure that user have permission to read underlying table(s), for instance: USE AdventureWorks; GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo...(
read more)
Read the complete post at http://sqlserver-qa.net/blogs/perftune/archive/2007/09/10/1960.aspx