更改sql server數(shù)據(jù)庫所有者的對象
添加時(shí)間:2013-3-8 17:27:02
添加:
思海網(wǎng)絡(luò)
--功能說明:成批更改數(shù)據(jù)庫所有者的對象
--用法:exec ChangeObjectOwner 'nmkspro','dbo'
--即可將所有nmkspro所有者的對象改為dbo所有
--運(yùn)行成功后將提示:"注意: 更改對象名的任一部分都可能破壞腳本和存儲過程。"
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
關(guān)鍵字:sql server、數(shù)據(jù)庫、所有者、對象
--用法:exec ChangeObjectOwner 'nmkspro','dbo'
--即可將所有nmkspro所有者的對象改為dbo所有
--運(yùn)行成功后將提示:"注意: 更改對象名的任一部分都可能破壞腳本和存儲過程。"
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
關(guān)鍵字:sql server、數(shù)據(jù)庫、所有者、對象
新文章:
- CentOS7下圖形配置網(wǎng)絡(luò)的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統(tǒng)后丟失windows啟動項(xiàng)
- CentOS單網(wǎng)卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗(yàn)證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網(wǎng)打印機(jī)IP講解
- CentOS7使用hostapd實(shí)現(xiàn)無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網(wǎng)絡(luò)重啟出錯(cuò)
- 解決Centos7雙系統(tǒng)后丟失windows啟動項(xiàng)
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統(tǒng)有什么不同呢
- Centos 6.6默認(rèn)iptable規(guī)則詳解