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

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

–改过有些表
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

主意一:直接点击该表-》设计-》属性-》改进下边包车型客车主人。该情势针对于表少的景色下。
方法二:利用脚本直接实行,用系统帐号或许超户登录到该数据库,然后推行下边语句:
sp_configure allow updates,1 go reconfigure with override go update
sysobjects set uid=1 where uid1
–依照条件得以随便校正,在多顾客之间切换。 go sp_configure allow
updates,0 go reconfigure with override

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

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

批量修正: EXEC sp_MSforeachtable exec sp_changeobjectowner ?,dbo
单个修改: exec sp_changeobjectowner 要改的表名,dbo

发表评论

电子邮件地址不会被公开。 必填项已用*标注