Best way to allow viewing of all SP's, tables etc
Posted on 2008-11-03
I have several people who would like to be able to view all Stored Procs (over 100), tables, views (basically all metadata) on ALL of the databases within a SQL instance in SQL 2000 & SQL 2005.
Rather than give them 'dbo' privilege on each database what would be the best way to allow them to view this sort of info.
I know SQL 2005 has the ability to grant the VIEW ANY DATABASE permission to a user but I also came across the GRANT VIEW ON DEFENITION SCHEMA option as well. Does SQL 2000 have anything similar?
What would be the best way to allow them to view all the metadata info but still keep the environment secure?