修改数据库中表和存储过程的所有者

修改数据库中表和存储过程的所有者

建立下面两个存储过程
在VS2003里运行第一个changename，输入的参数‘原来的用户名’，‘dbo’
再运行第二个存储过程ChangeObjectOwner，输入的参数‘原来的用户名’,‘dbo’

方法二（没试验过）
建立以下两个存储过程
以SA登陆查询分析器 ，选中你要的数据库 
执行存储过程 
执行exec Changename  ' 原所有者 ' , ' dbo '  
执行exec ChangeObjectOwner  ' 原所有者 ' , ' dbo '  
///
if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[changename] ' )  and   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 ) 
drop   procedure   [ dbo ] . [ changename ]  
GO  

SET  QUOTED_IDENTIFIER  OFF  
GO  
SET  ANSI_NULLS  OFF  
GO  

Create   PROCEDURE  dbo.changename 
@OldOwner   as   NVARCHAR ( 128 ), -- 参数原所有者 
@NewOwner   as   NVARCHAR ( 128 ) -- 参数新所有者 
AS  

DECLARE   @Name   as   NVARCHAR ( 128 ) 
DECLARE   @Owner   as   NVARCHAR ( 128 ) 
DECLARE   @OwnerName   as   NVARCHAR ( 128 ) 

DECLARE  curObject  CURSOR   FOR  
select   ' Name '   =  name, 
' Owner '   =   user_name (uid) 
from  sysobjects 
where   user_name (uid) = @OldOwner  
order   by  name 

OPEN  curObject 
FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner  
WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN    
if   @Owner = @OldOwner  
begin  
set   @OwnerName   =   @OldOwner   +   ' . '   +   rtrim ( @Name ) 
exec  sp_changeobjectowner  @OwnerName ,  @NewOwner  
end  

FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner  
END  

close  curObject 
deallocate  curObject 
GO  
SET  QUOTED_IDENTIFIER  OFF  
GO  
SET  ANSI_NULLS  ON  
GO  

///
CREATE   PROCEDURE  dbo.ChangeObjectOwner 
@OldOwner   as   NVARCHAR ( 128 ), -- 参数原所有者 
@NewOwner   as   NVARCHAR ( 128 ) -- 参数新所有者 
AS  
DECLARE   @Name   as   NVARCHAR ( 128 ) 
DECLARE   @Owner   as   NVARCHAR ( 128 ) 
DECLARE   @OwnerName   as   NVARCHAR ( 128 ) 
DECLARE  curObject  CURSOR   FOR  
select   ' Name '   =  name, 
' Owner '   =   user_name (uid) 
from  sysobjects 
where   user_name (uid) = @OldOwner  
order   by  name 
OPEN  curObject 
FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner  
WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN  
if   @Owner = @OldOwner  
begin  
set   @OwnerName   =   @OldOwner   +   ' . '   +   rtrim ( @Name ) 
exec  sp_changeobjectowner  @OwnerName ,  @NewOwner  
end  
FETCH   NEXT   FROM  curObject  INTO   @Name ,  @Owner  
END  
close  curObject 
deallocate  curObject 
GO