Replace Object Owner

--$History: $ sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- Drop Procedure
--------------------------------------------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_ReplaceObjectOwner' AND type = 'P')
   DROP PROCEDURE sp_ReplaceObjectOwner
GO

--------------------------------------------------------------------------------------------------------------------------------------------
-- Create procedure 
--------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROC sp_ReplaceObjectOwner As

--------------------------------------------------------------------------------------------------------------------------------------------
-- OBJECT NAME:sp_ReplaceObjectOwner
--------------------------------------------------------------------------------------------------------------------------------------------
-- AUTHOR:Vijayakumar G
--------------------------------------------------------------------------------------------------------------------------------------------
-- CREATED:04-04-2005
--------------------------------------------------------------------------------------------------------------------------------------------
/* DESCRIPTION:    This SP is used to identify and replace the owner of the database objects. This SP will give display object id, 
object name, owner id, owner name and concatenate object + owner name with brackets.  SP does change the owner for the following objects.
SP should available in "master" database to execute in more than one database.  
1.Table
2. Stored Procedures
3. Function
4.View
We can later add / modify the filter condition */--------------------------------------------------------------------------------------------------------------------------------------------
-- PARAMETERS:     None
--
--------------------------------------------------------------------------------------------------------------------------------------------
-- RETURNS:        0                -  success
--                 <To be Retrieves>
--                 returns from SQL Server
--------------------------------------------------------------------------------------------------------------------------------------------

-- VERSION: $Revision: $   UPDATED: $Modtime: $ 
--------------------------------------------------------------------------------------------------------------------------------------------
set nocount on

DECLARE @Object_ID Int, @Object_Name Sysname, 
@Owner_ID Int, @Owner_Name Sysname, @Owner_Object_Name Sysname 

Declare @New_Owner_Name Sysname
Set @New_Owner_Name = 'dbo'
DECLARE CUR_ReplaceObjectOwner CURSOR FOR 

Select 
SO.ID As "Object ID", SO.Name As "Object Name", 
SO.UID As "Owner ID", SU.Name As "Owner Name",
'[' + SU.Name + '].' + '[' + SO.Name + ']' As "Owner + Object Name"
From 
SysObjects SO, SysUsers SU
Where 
SO.UID = SU.UID And 
SO.xType In ('U','P', 'FN','V') And 
SO.Name Not Like '%dt_%' And 
SO.Name Not In ('syssegments', 'sysconstraints')
Order By xType


OPEN CUR_ReplaceObjectOwner

FETCH NEXT FROM CUR_ReplaceObjectOwner 
INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name

WHILE @@FETCH_STATUS = 0
BEGIN
If @New_Owner_Name != @Owner_Name
exec sp_changeobjectowner @Owner_Object_Name, @New_Owner_Name
If @@Error != 0
Begin
Return 1
End 
   -- Get the next author.
   FETCH NEXT FROM CUR_ReplaceObjectOwner 
   INTO @Object_ID, @Object_Name, @Owner_ID, @Owner_Name, @Owner_Object_Name
END

CLOSE CUR_ReplaceObjectOwner
DEALLOCATE CUR_ReplaceObjectOwner

set nocount off
return 0

GO