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 @mSql1varchar(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.