brief: 改变数据库所有对象的所有者
author: 吴尔平
date: 2005/12/19
*/
declare @owner varchar(255)
set @owner = 'dbo'
declare @objectname varchar(255)
declare @uid int
declare object_cursor cursor for select [name],uid from sysobjects
where OBJECTPROPERTY (id, 'IsMSShipped') = 0
and (xtype like '%') -- 如果要指定对象的类型，参考 Transact-SQL 帮助-> sysobjects-> xtype
open object_cursor
fetch next from object_cursor into @objectname, @uid
while(@@fetch_status=0)
begin
declare @uidName varchar(255)
select @uidName = [name] from dbo.sysusers where uid = @uid
if @uidName <> @owner
begin
--生成 '所有者.对象名' 格式的字符串
set @objectname = @uidName + '.' + @objectname
--改变对象所有者
exec sp_changeobjectowner @objectname, @owner
end
fetch next from object_cursor into @objectname, @uid
end
close object_cursor
deallocate object_cursor
go
