Tuesday, December 9, 2008

How to change all Object Owners to dbo

CreateALTER proc [dbo].[ChangeAllObjectOwnersTodbo]
as
set nocount on

declare @uid int
declare @objName varchar(50)
declare @userName varchar(50)
declare @currObjName varchar(50)
declare @outStr varchar(256)
set @uid = user_id('dbo')

declare chObjOwnerCur cursor static
for
select user_name(uid) as 'username', [name] as 'name' from sysobjects where uid <> @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
print 'All objects are already owned by dbo!'
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end

fetch next from chObjOwnerCur into @userName, @objName
while @@fetch_status = 0
begin
set @currObjName = 'dbo.' + @objName
if (object_id(@currObjName) > 0)
print 'WARNING *** ' + @currObjName + ' already exists ***'
set @outStr = 'sp_changeobjectowner ''' + @userName + '.' + @objName + ''', ''dbo'''
print @outStr
print 'go'
fetch next from chObjOwnerCur into @userName, @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0

No comments: