Permissions required for sp_databases

This may be the wrong forum for this - if so, please direct me to the appropriate forum.

I've recently upgraded to MSSQL 2005 and I'm trying to execute the sp_databases stored procedure, but I'm having what looks like permissions problems. I can execute the stored procedure, but it doesn't return any results. As far as I can tell, the problem is that my userid can't see any rows in sys.master_files (I can select from the view, it just doesn't return any rows).

I've tried any number of things to get this to work, but the only thing that's been successful is to change my userid's database role membership to db_owner. In the long run, this isn't really a viable solution for me, since my userid should only really have read(select) access to the database in question.

I'd appreciate any pointers that you can give me - I figure I'm doing something stupid, but haven't been able to figure out what, yet.

Thanks,
Jeff

If you grant VIEW ANY DEFINITION, you will be able to see this information for the databases that you can access. However, VIEW ANY DEFINITION will open other catalogs as well.

I suggest to write a wrapper procedure over sp_databases that is signed with a certificate and grant that certificate the VIEW ANY DEFINITION permission, then grant EXECUTE permission on the procedure to the principal that needs this functionality.

Thanks
Laurentiu

|||That seems to work, thanks!

Is there any way to grant permission for an individual database? My user is really only interested in the sp_databases row for a single database. The others are just ignored, so they could be dropped out of the result set with no loss of functionality (and that might limit the permissions that need to be granted).

Jeff
|||

You can use a signed procedure to filter the results and only display the row for a certain database. You cannot do this only using permissions.

Thanks
Laurentiu

|||So if a procedure is created as dbo a user who has readonly access to the db in which it was created wouldn't be able to query sysobjects or use sp_helptext to view procedure information? In 2000 the user would be able to see the procedure but this is not so in 2005.