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

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 procedur开发者_C百科es.

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?

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]

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

are you using the database? the correct one?

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