Jerry Nixon @Work: Reset SQL 2000 objects to DBO

Jerry Nixon on Windows

Wednesday, January 24, 2007

Reset SQL 2000 objects to DBO

It sometimes happens that developers will log in with non-dbo credentials and create some objects, resulting in the object's owner being incorrect. Here's a simple script to reset all objects in your database to dbo.

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