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

核心提示：更改某个表exec sp_changeobjectowner 'tablename','dbo'存储更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwneras NVARCHAR(128),@NewOwneras NVA

更改某个表

exec sp_changeobjectowner 'tablename','dbo'

存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwneras NVARCHAR(128),
@NewOwneras 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