6

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

Venki
  • 2,129
  • 6
  • 32
  • 54
  • You could create a view or function which returns the results and grant privalages to the view/function. – xQbert Dec 02 '11 at 12:32
  • If I add the user as db_owner would that help ? I don't want to create a view / function – Venki Dec 02 '11 at 12:34
  • Try using sys.All_objects seems when MSFT went from 2000 to 2005/08 permissions on system tables were altered to create a more secure environment http://stackoverflow.com/questions/3492269/sql-server-2005-2008-why-is-the-sys-sysobjects-view-available-to-users-without – xQbert Dec 02 '11 at 12:56

1 Answers1

10

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.

Brian Knight
  • 4,970
  • 28
  • 34
  • 1
    Example with sys.databases where it seems not intuitive: Logged in as abc, USE-ing DB X, sys.databases does not show DB Y despite abc *owning* that DB Y. How does that make sense? – Don Cheadle Aug 07 '18 at 22:33