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

Rate

Share

Share

Rate