一次性更改数据库內拥有者不屬dbo的物件改成为dbo

Create PROCEDURE SP_ChangeObjectOwnerToDBO 
AS
SET NOCOUNT ON

DECLARE @objName varchar(80),    @ownerName varchar(80), @Parameter    varchar(80)

DECLARE object_cursor CURSOR FOR

-- Find out table list not belong owner
select o.name, user_name(o.uid)
from dbo.sysobjects o, dbo.sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id

and i.indid < 2 and o.name not like N'#%' and xtype='U'
and user_name(o.uid)<>'dbo' order by o.name

OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @objName, @ownerName

PRINT''
PRINT 'Process Table(s) '
PRINT '--------------------------------------------'

IF @@FETCH_STATUS <> 0 
PRINT 'No Objects Affected.'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Parameter = @ownerName+'.'
PRINT @Parameter

-- Change Object Owner ---
exec sp_changeobjectowner @Parameter , 'dbo'

FETCH NEXT FROM object_cursor INTO @objName, @ownerName
PRINT''
END

CLOSE object_cursor
DEALLOCATE object_cursor
-------------------

DECLARE object_cursor CURSOR FOR

-- Find out View list not belong owner
select o.name, user_name(o.uid)
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsView') = 1 and o.name not like N'#%%' 
and user_name(o.uid)<>'dbo' order by o.name

OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @objName, @ownerName

PRINT''
PRINT 'Process View(s) '
PRINT '--------------------------------------------'

IF @@FETCH_STATUS <> 0 
PRINT 'No Objects Affected.'

WHILE @@FETCH_STATUS = 0
BEGIN

select @Parameter = @ownerName+'.'
print @Parameter

-- Change Object Owner ---
exec sp_changeobjectowner @Parameter , 'dbo'

FETCH NEXT FROM object_cursor INTO @objName, @ownerName
print ''
END

CLOSE object_cursor
DEALLOCATE object_cursor
--------------------------------

DECLARE object_cursor CURSOR FOR

-- Find out Store Proc    list not belong owner
select o.name, user_name(o.uid)
from dbo.sysobjects o where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id,

N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1) and o.name not like N'#%%'   
and user_name(o.uid)<>'dbo' order by o.name

OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @objName, @ownerName

PRINT''
PRINT 'Process Store Procedure(s) '
PRINT '--------------------------------------------'

IF @@FETCH_STATUS <> 0 
PRINT 'No Objects Affected.'

WHILE @@FETCH_STATUS = 0
BEGIN

select @Parameter = @ownerName+'.'
print @Parameter

-- Change Object Owner ---
exec sp_changeobjectowner @Parameter , 'dbo'

FETCH NEXT FROM object_cursor INTO @objName, @ownerName
PRINT''
END

CLOSE object_cursor
DEALLOCATE object_cursor

-----------------------------------------

DECLARE object_cursor CURSOR FOR

-- Find out User Define Func list not belong owner
select o.name, user_name(o.uid)
from dbo.sysobjects o          where (o.xtype = N'TF' or o.xtype = N'FN' or o.xtype = N'IF') and

o.name not like N'#%%'  
and user_name(o.uid)<>'dbo' order by o.name

OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @objName, @ownerName

PRINT''
PRINT 'Process User Define Function(s) '
PRINT '--------------------------------------------'

IF @@FETCH_STATUS <> 0 
PRINT 'No Objects Affected.'

WHILE @@FETCH_STATUS = 0
BEGIN

select @Parameter = @ownerName+'.'
print @Parameter

-- Change Object Owner ---
exec sp_changeobjectowner @Parameter , 'dbo'

FETCH NEXT FROM object_cursor INTO @objName, @ownerName
PRINT''
END

CLOSE object_cursor
DEALLOCATE object_cursor

Set Nocount off
GO