虛擬主機遷移、Schema 整理、權限重構時,常需將資料庫物件 (Table, View, SP, Function) 擁有者從舊用戶 (如 `nmkspro`) 統一變更為 `dbo`。本文提供三種方案:系統預存程序、游標批次、系統表直改 (高風險)。
EXEC sp_changeobjectowner '舊擁有者.物件名', '新擁有者';
-- 範例
EXEC sp_changeobjectowner 'nmkspro.Orders', 'dbo';
CREATE PROCEDURE dbo.ChangeObjectOwner
@OldOwner NVARCHAR(128), -- 原擁有者
@NewOwner NVARCHAR(128) -- 新擁有者
AS
BEGIN
DECLARE @Name NVARCHAR(128), @Owner NVARCHAR(128), @OwnerName NVARCHAR(128)
DECLARE curObject CURSOR FOR
SELECT name, 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
PRINT 'Changed: ' + @OwnerName + ' -> ' + @NewOwner
END
FETCH NEXT FROM curObject INTO @Name, @Owner
END
CLOSE curObject
DEALLOCATE curObject
END
GO
-- 執行
EXEC ChangeObjectOwner 'nmkspro', 'dbo'
-- 修改所有表
EXEC sp_MSforeachtable 'EXEC sp_changeobjectowner ''?'', ''dbo'' '
-- 修改所有視圖
EXEC sp_MSforeachview 'EXEC sp_changeobjectowner ''?'', ''dbo'' '
-- 修改所有預存程序
EXEC sp_MSforeachproc 'EXEC sp_changeobjectowner ''?'', ''dbo'' '
-- ⚠️ 高風險操作,需 sysadmin,建議備份 master 後操作
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
-- 將所有非 dbo 擁有者的物件改為 dbo (uid=1)
UPDATE sysobjects SET uid = 1 WHERE uid <> 1
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
同樣適用於 View、SP、Function (在對應節點右鍵設計/屬性)。
GRANT/DENY 權限是否正確繼承-- 檢查非 dbo 擁有者物件
SELECT
SCHEMA_NAME(schema_id) AS Owner,
type_desc AS Type,
name AS ObjectName
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) <> 'dbo'
ORDER BY Owner, Type, name;
-- 檢查特定 Schema 物件數
SELECT SCHEMA_NAME(schema_id) AS Owner, type_desc, COUNT(*)
FROM sys.objects
GROUP BY SCHEMA_NAME(schema_id), type_desc
ORDER BY Owner;
SQL Server 物件擁有者變更:
✅ **首選**:`sp_changeobjectowner` + `sp_MSforeachtable/view/proc` 批次,標準、安全、支援所有版本
⚠️ **次選**:SSMS 圖形化,適合少量手工操作
🚫 **禁用**:`sysobjects.uid` 直改,高風險、不支援新版、違反支援政策
📋 **驗證**:變更前後對比 `sys.objects` Schema 分佈,應用端全量回歸測試