sql server 2000 change ownership of tables | OpenKB::Open Access Technical Information

Published by exdone

Posted on February 24, 2008

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

SELECT
  @old = 'OLDUSERNAME'
  , @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

======================================

changing stored procs

========================

SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+ 
  ltrim(u.name) + '.' + ltrim(s.name) + '''''' 
  + ', @newowner = dbo'')'
FROM  sysobjects s, 
      sysusers u
WHERE s.uid = u.uid
AND   u.name <> 'dbo'
AND   xtype in ('V', 'P', 'U')
AND   u.name not like 'INFORMATION%'
order by s.name

This query finds every view, stored procedure and user table in the database not owned by the dbo and converts ownership to the dbo. The output looks like this:

EXEC('sp_changeobjectowner @objname = ''lname.Authors'', @newowner = dbo')
EXEC('sp_changeobjectowner @objname = ''lname.BANNER_Ads'', @newowner = dbo')
EXEC('sp_changeobjectowner @objname = ''lname.Comments'', @newowner = dbo')

You can simply copy and paste the EXEC statements into Query Analyzer and run it. This might have been overkill for fifteen objects in my database. At work I deal with a database that has over 6,000 stored procedures and approaches like this are a little more appropriate. Enjoy.

Reference

Categories: Uncategorized