--執(zhí)行這個(gè)語(yǔ)句,就可以把當(dāng)前庫(kù)的所有表的所有者改為dbo
exec sp_msforeachtable ’sp_changeobjectowner ’’?’’, ’’dbo’’’
--如果是要用戶表/存儲(chǔ)過程/視圖/觸發(fā)器/自定義函數(shù)一齊改,則用游標(biāo)(不要理會(huì)錯(cuò)誤提示)
另:
exec sp_msforeachtable ’sp_changeobjectowner ’’?’’, ’’dbo’’’
--如果是要用戶表/存儲(chǔ)過程/視圖/觸發(fā)器/自定義函數(shù)一齊改,則用游標(biāo)(不要理會(huì)錯(cuò)誤提示)
declare tb cursor local for
select ’sp_changeobjectowner ’’[’+replace(user_name(uid),’]’,’]]’)+’].[’
+replace(name,’]’,’]]’)+’]’’,’’dbo’’’
from sysobjects
where xtype in(’U’,’V’,’P’,’TR’,’FN’,’IF’,’TF’) and status>=0
open tb
declare @s nvarchar(4000)
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
go
select ’sp_changeobjectowner ’’[’+replace(user_name(uid),’]’,’]]’)+’].[’
+replace(name,’]’,’]]’)+’]’’,’’dbo’’’
from sysobjects
where xtype in(’U’,’V’,’P’,’TR’,’FN’,’IF’,’TF’) and status>=0
open tb
declare @s nvarchar(4000)
fetch tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch tb into @s
end
close tb
deallocate tb
go
另:
--功能說明:成批更改數(shù)據(jù)庫(kù)所有者的對(duì)象
--作者:不詳
--用法:exec ChangeObjectOwner ’nmkspro’,’dbo’
--即可將所有nmkspro所有者的對(duì)象改為dbo所有
--運(yùn)行成功后將提示:"注意: 更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。"
CREATE PROCEDURE dbo.ChangeObjectOwner
@OldOwner as NVARCHAR(128),--參數(shù)原所有者
@NewOwner as NVARCHAR(128)--參數(shù)新所有者
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
--作者:不詳
--用法:exec ChangeObjectOwner ’nmkspro’,’dbo’
--即可將所有nmkspro所有者的對(duì)象改為dbo所有
--運(yùn)行成功后將提示:"注意: 更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。"
CREATE PROCEDURE dbo.ChangeObjectOwner
@OldOwner as NVARCHAR(128),--參數(shù)原所有者
@NewOwner as NVARCHAR(128)--參數(shù)新所有者
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