Need to grant permission to view stored procedure text
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
TO Recruiting11;
GO
Whereas to view the text within the SQL 2005 you need to grant them VIEW DEFINITION on the procedures. To make it simpler you grant them this permission on the schema:
GRANT VIEW DEFINITION ON SCHENA::dbo TO someuser
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.