更改数据库中表的所属用户的两个方法

更改某个表

exec sp_changeobjectowner 'tablename','dbo'

存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch

@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

-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner

END

close curObject

deallocate curObject

GO