Change ownership – SQL server

DB TAbLES
=========
DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
  @old = ‘oldOwner_CHANGE_THIS’
  , @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

STORED PROCEDURES
=================
DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = ‘oldOwner’
, @new = ‘dbo’
, @sql = ‘
IF EXISTS (SELECT NULL FROM information_schema.routines
WHERE
QUOTENAME(ROUTINE_SCHEMA)+”.”+QUOTENAME(ROUTINE_NAME) = ”?”
AND ROUTINE_SCHEMA = ”’ + @old + ”’
)
EXECUTE sp_changeobjectowner ”?”, ”’ + @new + ””

EXECUTE sp_MSforeachtable @sql

———————————————————————————–

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: