SQL Server 2005 sp_helptext change.

In the earlier version of MS SQL Server (Before SQL Server 2005) , the data in the system tables was visible to any user. But always it is good to have some other tools to view meta data.
We were using sps such as to view the meta data/definition/script of the objects.

• sp_help
• sp_helptext

But in SQL Server 2005 this will not work to any user unless it is explicitly specified. Here you will receive an error

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

If you want it to work like legacy, and does not want to restrict metadata visibility, a new SQL Server 2005 permission can be used. The permission, VIEW DEFINITION, allows a user or role to see the definition of an object or all objects within a particular scope. The below mentioned sql statement can be used to do the same

• GRANT VIEW ANY DEFINITION TO [user]

But be careful while giving these kind of access to any users. Unless it is required do not GRANT such privileges

For more details please visit
http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/

Bharat Mane