[SQL Server] 소유자 변경

* Table 소유자 변경

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT

    @old = '이전 소유자명'

  , @new = 'dbo'

  , @sql = '

  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES

  WHERE

      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''

      AND TABLE_SCHEMA = ''' + @old + '''

  )

  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

* View 소유자 변경

  결과로 출력되는 내용을 C&P 해서 쿼리창에서 다시 실행

DECLARE @old_view_owner sysname, @new_view_owner sysname

SELECT

      @old_view_owner = '이전 소유자명'

    , @new_view_owner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)+''','''+@new_view_owner+''''

from

    INFORMATION_SCHEMA.VIEWS a

where

    a.TABLE_SCHEMA = @old_view_owner

    AND

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)), 'IsMSShipped') = 0

* 저장프로시저 소유자 변경

  결과로 출력되는 내용을 C&P 해서 쿼리창에서 다시 실행

DECLARE @old_sp_owner sysname, @old_new_owner sysname

SELECT

      @old_sp_owner = '이전 소유자명'

    , @old_new_owner = 'dbo'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@old_new_owner+''''

from

    INFORMATION_SCHEMA.ROUTINES a

where

    a.ROUTINE_TYPE = 'PROCEDURE'

    AND a.SPECIFIC_SCHEMA = @old_sp_owner

    AND

OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0