When you need to change the schema for SPs.
Remember this:
DECLARE@OldOwner sysname,@NewOwner sysnameSET @OldOwner = 'oldOwner'SET @NewOwner = 'dbo'DECLARE CURS CURSOR FORSELECTnameFROMsysobjectsWHEREtype = 'p'ANDuid = (SELECT uid FROM sysusers WHERE name = @OldOwner)ANDNOT name LIKE 'dt%' FOR READ ONLYDECLARE @ProcName sysnameOPEN CURSFETCH CURS INTO @ProcNameWHILE @@FETCH_STATUS = 0BEGINIF @@VERSION >= 'Microsoft SQL Server 2005'BEGINEXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')ENDELSEEXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')FETCH CURS INTO @ProcNameENDCLOSE CURSDEALLOCATE CURS
Courtesy of Tim Page; http://www.geekzilla.co.uk/ViewC1D86C71-13E7-477B-94EC-FD01B524B6C3.htm
k