Permission to query dbo.sysobjects

Question

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

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 get testForEmpty = 1. This meant that the if exists call returns empty lines.

However, if I add the user as sysadmin, then I get the value of testFormEmpty equal to 0 and at least one row is selected.

I do not want to add the user to sysadmin. What is the minimum role / permissions that I must grant so that a selection from dbo.sysobjects returns the contents.

thanks

Answer 1

You must use sys.object first instead of dbo.sysobjects. dbo.sysobjects is a SQL 2000 constructor that is only in SQL 2008 for backward compatibility reasons. sys.objects contains a string for each user-defined object with a schema area that is created in the database, so you won’t need to filter your request at all. sys.all_objects is a superset containing both system and user objects.

The second — on the permission side — in SQL Server 2005 and later, the visibility of metadata in catalog views is limited to the protection that the user either owns or that the user has been granted permission. Thus, your user must be granted some permission for the items that he is looking for. Providing VIEW DEFINITION to the user of the schema (s) in the database will allow the request to work without providing access to any data.