Replace Object Owner

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.

--$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
--
-- 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