How come I can't see rows in sysobjects for objects that I know exist?

Question

I am a member only of the db_datareader role on a database, and I cannot see rows in sysobjects for stored procedures that I know exist.

Additionally, in SQL Server Managedemnt Studio, expanding the Programmability -> Stored Procedures node in Object Explorer does not show any stored procedures.

Is this due to insufficient permissions? If so, what permissions do I need in order to be able to see all rows in sysobjects, and also to see what permissions have been granted on each object?

Answer 1

are you using the database? the correct one?

use [db_x]
go
select * from sysobjects
where type = 'P'

Answer 2

You're probably lacking VIEW DEFINITION permission. To get permission on an entire schema, use:

GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]

For an individual procedure:

GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]

Answer 3

Hi Daniel, What you are seeing is related to a permissions issue. Try adding yourself to this role db_ddladmin for that database.

Info on db_ddladmin: http://msdn.microsoft.com/en-us/library/ms190667(SQL.90).aspx