修改表的所有者

1. 修改表的所有者

declare @tn varchar(120)

declare table_cursor cursor for
Select '[' + sysusers.name + '].' + sysobjects.name AS table_name
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysusers.name = 'YourUserName' AND sysobjects.type = 'U'

open table_cursor
fetch next from table_cursor into @tn

while @@FETCH_STATUS = 0
begin
   exec sp_changeobjectowner @tn, 'dbo'

   fetch next from table_cursor into @tn
end

close table_cursor
deallocate table_cursor


2. 修改存储过程的所有者

declare @tn varchar(120)

declare procedure_cursor cursor for
Select '[' + sysusers.name + '].' + sysobjects.name AS procedure_name
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysusers.name = 'YourUserName' AND sysobjects.type = 'P'

open procedure_cursor
fetch next from procedure_cursor into @tn

while @@FETCH_STATUS = 0
begin
   exec sp_changeobjectowner @tn, 'dbo'

   fetch next from procedure_cursor into @tn
end

close procedure_cursor
deallocate procedure_cursor


3. 修改视图的所有者

declare @tn varchar(120)

declare view_cursor cursor for
Select '[' + sysusers.name + '].' + sysobjects.name AS view_name
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysusers.name = 'YourUserName' AND sysobjects.type = 'V'

open view_cursor
fetch next from view_cursor into @tn

while @@FETCH_STATUS = 0
begin
   exec sp_changeobjectowner @tn, 'dbo'

   fetch next from view_cursor into @tn
end

close view_cursor
deallocate view_cursor