Database Knowledge Base / MS SQL Server /
Email to friend
* Your name:
* Your email:
* Friend's email:
Comment:


How to change the owner of all tables in a database?

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
  @oldowner = 'OLD_OWNER',
  @newowner = 'dbo',
  @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @oldowner + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql




RSS