Binaryworld - Change Object Owner ... [ SQL Server -> Stored Procedure ]

This is very handy stored procedure which I use all time to change object owner. Many times you create object as a different user other than "dbo" and things start breaking. I use this sp to change owner off all objects to "dbo".

Click here to copy the following block

/*
This stored procedure can be used to run through all of a specific
database's objects owned by the 'oldowner' and change the old
owner with the new one.
You should pass the old owner name and the new owner name,
as in the example below:

EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex'
*/

IF OBJECT_ID('ChangeAllObjOwner') IS NOT NULL DROP PROC ChangeAllObjOwner
GO

CREATE PROCEDURE ChangeAllObjOwner (
@oldowner sysname,
@newowner sysname
)
AS
DECLARE @objname sysname
SET NOCOUNT ON

--check that the @oldowner exists in the database
IF USER_ID(@oldowner) IS NULL
BEGIN
  RAISERROR ('The @oldowner passed does not exist in the database', 16, 1)
  RETURN
END
--check that the @newowner exists in the database
IF USER_ID(@newowner) IS NULL
BEGIN
  RAISERROR ('The @newowner passed does not exist in the database', 16, 1)
  RETURN
END

DECLARE owner_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE uid = USER_ID(@oldowner)

OPEN owner_cursor
FETCH NEXT FROM owner_cursor INTO @objname
WHILE (@@fetch_status <> -1)
BEGIN
SET @objname = @oldowner + '.' + @objname
EXEC sp_changeobjectowner @objname, @newowner
FETCH NEXT FROM owner_cursor INTO @objname
END

CLOSE owner_cursor
DEALLOCATE owner_cursor
GO