Search This Blog

Change all database object owners to DBO

declare @ObjectName varchar(256)
-- we are only interested in USER Objects
-- not already owned by 'sa'
-- we don't want keys and constrainst
set @ObjectName = (
select top 1 [name] from sysobjects
where uid <> SUSER_SID('sa')
and [type] in ('FN','IF','P','TF','U','V')
)
declare @ObjectOwner varchar(256)
declare @ObjectFullName varchar(512)
declare @NewOwner varchar(256)
set @NewOwner = 'dbo'

-- default to 'dbo' if null
set @NewOwner = isnull(@NewOwner, 'dbo')

while @ObjectName is not null
begin
select @ObjectOwner = USER_NAME(uid)
from sysobjects where [name] = @ObjectName
set @ObjectFullName = @ObjectOwner + '.' + @Objectname
PRINT 'Changing ownership of ''' + @Objectname +
''' from ''' + @ObjectOwner + ''' to ''' +
@NewOwner + ''''
execute sp_changeobjectowner @ObjectFullName, @NewOwner
set @ObjectName = (select top 1 [name] from sysobjects
where uid <> SUSER_SID('sa')
and [type] in ('FN','IF','P','TF','U','V'))
end

No comments: