Finding Objects Owned by non-DBO Users

In this article I will show how can we get the list of all the objects (table, Procedure, view or user defined function) owned by non DBO users. I believe this occurred most on Development server where developers doesn’t have DBO rights in Database. To get the list of object we need to create below table and procedure.

Step 1: Create table ObjList using below script:

CREATE
TABLE [ObjList] (
	[DBName]
[sysname] NOT NULL ,
	[Object_Type]
[varchar] (20)  NULL ,
	[ObjectOwner]
[sysname] NOT NULL ,
	[ObjectName]
[sysname] NOT NULL ,
	[cur_date]
[datetime] NOT NULL CONSTRAINT [DF_ObjList_cur_date] DEFAULT
(getdate())
)
ON [PRIMARY]
GO

Step 2: Create Procedure Object_owned_by_non_dbo using below script

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
Create Procedure Object_owned_by_non_dbo
as
declare @dbname varchar(200)
declare @mSql1	varchar(8000)
Set nocount on

DECLARE DBName_Cursor CURSOR FOR 
	select name 
	from
	master.dbo.sysdatabases 
	where name not in ('master','msdb','tempdb')
	Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
 BEGIN 
  Set @mSql1 = 'Insert into  ObjList (DBName, Object_Type, ObjectOwner,ObjectName)'+char(13)
  Set @mSql1 = @mSQL1+'Select '''+@dbname+''' as dbName,ObjType = 
	Case xtype 
		when ''u'' then ''Table''
		when ''V'' then ''View''
		when ''P'' then ''Procedure''
		when ''FN'' then ''UD Function''
		else xtype end
	, SU.name,SO.name  from '+@dbname+'.dbo.sysobjects SO join '+@dbname+'.dbo.sysusers SU 
	on SO.uid = SU.uid and  su.name <> ''dbo''  
	and SO.xtype in (''u'',''v'',''p'',''FN'')'

  --Print @mSql1
  Execute (@mSql1)

  FETCH NEXT FROM DBName_Cursor INTO @dbname 
 END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Step 3: Please execute the below script to retrieve the list.

Select * from ObjList


DBName      Object_Type  ObjectOwner    ObjectName       cur_date
----------  ------------ -------------  ---------------- -----------------------
Database1   UD Function  Userv1         IS_FIELD_IN      2004-10-29 16:42:39.127
Database2   Table        Domain1\NT1    Vendor_Top200    2004-10-29 16:42:39.813
Database2   Table        Domain1\NT1    Supplier_Top200  2004-10-29 16:42:39.813
Database2   Table        Domain1\NT1    Emp_Top200       2004-10-29 16:42:39.813

Step 3: We can confined the list for specific user or database as below:

Select * from ObjList where DBName = 'Database1'

DBName     Object_Type    ObjectOwner    ObjectName        cur_date               
---------- -------------- -------------- ----------------- -----------------------
Database1  UD Function    Userv1         IS_FIELD_IN       2004-10-29 16:42:39.127

Or

Select * from ObjList where ObjectOwner = 'Domian1\NT1'

DBName     Object_Type    ObjectOwner    ObjectName      cur_date               
---------- -------------- -------------- --------------- -----------------------
Database1  UD Function    Userv1         IS_FIELD_IN     2004-10-29 16:42:39.127
Database2  Table          Domain1\NT1    Vendor_Top200   2004-10-29 16:42:39.813
Database2  Table          Domain1\NT1    Supplier_Top200 2004-10-29 16:42:39.813
Database2  Table          Domain1\NT1    Emp_Top200      2004-10-29 16:42:39.813

I think you will like this procedure. Please don’t forget to send me your comments on this article.