Change Table Owners in SQL

If you're doing a path code refresh, you know it's faster just to backup and restore the databases through SQL rather than use R98403. What I've found is a lot of CNCs still use R98403 because they're not sure how to straighten things out after a restore.

You see, when you back up JDE_PRODUCTION (for example) and restore it as JDE_CRP, it doesn't bother to change the owners. The R98403 will do that for you, but it will take three weeks to finish it.

I found this awesome script that will change all the table owners quick and easy. I got it from Scott Forsythe over at http://weblogs.asp.net/owscott/

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

SELECT

@old = 'OldOwner_CHANGE_THIS'

, @new = 'NewOwner_CHANGE_THIS'

, @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

This script will work in SQL 2005 and SQL 2008. It's the greatest!