Users cannot view stored procedure text

[traceable1]

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?

thanks for any insight!!
Tracy

[Erland Sommarskog]

traceable1 (tr*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[traceable1]

Excellent! Gonig back to the Oracle way of doing things, eh? I will
try it - thank you so much! :)

On Apr 4, 5:23 pm, Erland Sommarskog <[e-mail]:

traceable1 ([e-mail]) 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, [e-mail]

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