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

sql server 批量修改表和存储过程的所有者。批量修改表的所有者：    

EXEC    sp_MSforeachtable    ' exec   sp_changeobjectowner    '' ? '' , '' dbo ''     '   

 

  单个修改表所有者：  

      exec   sp_changeobjectowner   '要改的表名','dbo'  

批量修改存储过程的存储过程：

 

代码 CREATE     PROCEDURE    ChangeProcOwner      @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     and    xtype = ' p '       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

 

 

执行

 

exec    ChangeProcOwner    ' xx ' , ' dbo '  

 

或者

 

exec    ChangeProcOwner    ' ？ ' , ' dbo '  

 

 

转载于:https://www.cnblogs.com/284996867/archive/2010/03/16/1686862.html