SQL修改整个数据库对象所有者

 

declare @Db_Name_Old varchar(50)
declare @Db_Name_New varchar(50)
set @Db_Name_Old ='etoon' --原始用户
set @Db_Name_New='dbo'  --新用户

declare @uid int
--包括修改表和存储过程

declare @name varchar(50)
declare @XType varchar(50)         
declare @ydcardid varchar(50) 

--取得数据库用户ID
SELECT @uid=uid FROM Sysusers where name=@Db_Name_Old
--XType='U'   u表示用户表,p表示存储过程      
declare abc cursor         
for SELECT name,XType FROM SysObjects Where uid=@uid ORDER BY Name
open abc         
fetch next from abc into @name,@XType        
          
while @@fetch_status = 0 
begin
 declare @a nvarchar(4000)
 --当对象存在时删除
 if @XType='p'
  set @a='if object_id(''dbo.'+@name+''') is not null drop proc '+@name
 if @XType='u'
  set @a='if object_id(''dbo.'+@name+''') is not null drop table '+@name
 exec(@a)
 DECLARE @RC int
 DECLARE @objname nvarchar(517)
 DECLARE @newowner nvarchar(128)
 set @objname = @Db_Name_Old + '.'+ @name--旧所有者.对象名
 set @newowner =@Db_Name_New --新所有者
 EXEC @RC = [dbo].[sp_changeobjectowner] @objname, @newowner 
 DECLARE @PrnLine nvarchar(4000)
 PRINT '存储过程: master.dbo.sp_changeobjectowner'
 SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC)
 PRINT @PrnLine  
 fetch next from abc into @name,@XType        
end
close abc         
deallocate abc