Permission for querying dbo.sysobjects

Question

I have the following query to check whether there are any user defined objects in my SQL DB.

DECLARE @testForEmpty BIT
if exists 
    (select top 1 null from dbo.sysobjects where (objectproperty(id, 'IsMsShipped') = 0)) 
        set @testForEmpty = 0 
else set @testForEmpty = 1

When I run this query as a specific user, I am aways getting testForEmpty = 1. This meant that the if exists call returns empty rows.

However if I add the user as sysadmin, then I get the testFormEmpty value as 0 and atleast one row is getting selected.

I do not want to add the user as sysadmin. What is the minimum role / permissions that I should grant so that the select from dbo.sysobjects returns the content.

Thanks

Answer 1

First - you should use sys.objects instead of dbo.sysobjects. dbo.sysobjects is a SQL 2000 construct that is only in SQL 2008 for backward-compatibility reasons. sys.objects contains a row for each user-defined, schema-scoped object that is created within a database, so you wouldn't have to filter your query at all. sys.all_objects is a superset, that contains both system and user objects.

Second - on the permission side - in SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. So your user will have to be granted some permission on the items it is looking for. Granting VIEW DEFINITION to the user on the schema(s) in the database would allow the query to work, without granting access to any data.