Posted by Erland Sommarskog on 04/04/07 22:23

traceable1 (tracykc@gmail.com) writes:
> In SQL 2005 (we use Enterprise 64-bit SP2), the users cannot see the
> text of the stored procedures, functions, etc.
>
> This is a production database, so I cannot give them rights to modify
> them, but they need to be able to see what the procs are doing.
>
> I didn't have this problem in 2000 - how can I adjust the privileges
> to allow them to view the contents of the Programmability objects?

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

In SQL 2005, you don't have permissions to see system objects in the same
way you had in SQL 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]