ChangeAllObjectOwner

EXEC ChangeAllObjOwner @oldowner = @#John@#, @newowner = @#Alex@#

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

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  //line continous
                  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

原文转自：http://www.ltesting.net