DB개체의 모든 소유자 변경 > db

출처 : http://www.mssqlcity.com

내용 : 이전 소유자와 새 소유자를 넘겨주면 이전 소유자의 개체들을 찾아 자동으로 새 소유자로 변경해 줍니다.

참고 하시길 바랍니다.

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'

--------------------------------------------------------------------------------

파일 명 : ChangeAllObjOwner.sql

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