一次性更該所有物件所有者的儲存過程。

有時候以不同的使用者連線sql server 2000資料庫,建立的物件就不一樣，這是如果要刪除某個使用者，則需要把他所擁有的所有物件的所有者更該掉。

本儲存過程就是用來一次性更該所有物件的所有者的。

[@more@]

/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
- 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 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