在SQL Server中使用sp_changeobjectowner存盤程序修改物件所有者（Owner）時，可能會遇到下面錯誤資訊，當然物件klb.[k3]這個表確實是存在的，如下所示：
注意：個人測驗環境為SQL Server 2014的標準版，如有不同情況，請以實際環境為準，
USE AdventureWorks2014;GOEXEC sp_changeobjectowner 'klb.k3', 'dbo';
GO
Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 81
Object 'klb.[V_Test]' does not exist or is not a valid object for this operation.
當然你再次執行的話，可能會遇到同樣的錯誤提示，但是Line的值有所變化，變成了99. （這里是因為在腳本第二處拋出錯誤）
Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 99
Object 'klb.[k3]' does not exist or is not a valid object for this operation.
我們獲取sp_changeobjectowner存盤程序的定義如下：
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GOcreate procedure sys.sp_changeobjectowner
@objname nvarchar(776), -- may be "[owner].[object]" @newowner sysname -- must be entry from sysusersasSet nocount on
Set ansi_padding on
declare @objid int,
@newuid int, @ret int,@oldowner sysname,
@stmtS nvarchar(4000)
-- CHECK PERMISSIONS: Because changing owner changes both schema and
-- permissions, the caller must be one of:-- (1) db_owner
-- (2) db_ddladmin AND db_securityadminif (is_member('db_owner') = 0) and
(is_member('db_securityadmin') = 0 OR is_member('db_ddladmin') = 0)
beginEXEC %%System().AuditEvent(ID = 1094864724, Success = 0, TargetLoginName = NULL, TargetUserName = @newowner, Role = NULL, Object = @objname, Provider = NULL, Server = NULL)
raiserror(15247,-1,-1) return (1) end else beginEXEC %%System().AuditEvent(ID = 1094864724, Success = 1, TargetLoginName = NULL, TargetUserName = @newowner, Role = NULL, Object = @objname, Provider = NULL, Server = NULL)
endif parsename(@objname, 1) is null
begin raiserror(15253, -1, -1, @objname) return (1) endBEGIN TRANSACTION
-- RESOLVE OBJECT NAME (CANNOT BE A CHILD OBJECT: TRIGGER/CONSTRAINT) --
select @objid = object_id(@objname, 'local')if not (@objid is null)
beginEXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
if (@@error <> 0) -- lock failedselect @objid = null
endif (@objid is null) OR
(select parent_obj from sysobjects where id = @objid) <> 0 OR
ObjectProperty(@objid, 'IsSystemTable') = 1 OR
parsename(@objname, 3) is not null OR
parsename(@objname, 4) is not null OR
exists (select * from sys.objects where object_id = @objid and schema_id in (3,4)) OR -- INFORMATION_SCHEMA, sys
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
EXISTS (SELECT * FROM sysdepends d WHERE
d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id -- that isn't a self-reference (self-references don't use object name)
AND @objid <> -- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
)
begin-- OBJECT NOT FOUND
COMMIT TRANSACTION
raiserror(15001,-1,-1,@objname) return 1 end-- object's schema name must be the same as the schema owner's name
if not exists (select so.name
from sys.objects sojoin sys.schemas ss on so.schema_id = ss.schema_id
join sys.database_principals su on ss.principal_id = su.principal_id
where object_id = @objid and so.principal_id is null and ss.name = su.name)
begin-- OBJECT NOT FOUND
COMMIT TRANSACTION
raiserror(15001,-1,-1,@objname) return 1 endselect @oldowner = ssch.name from sys.schemas ssch join sys.objects so on (so.schema_id = ssch.schema_id) where object_id = @objid
-- SHARE LOCK OLD SCHEMA, PREVENT DROP OF THE OWNER WHILE TXN ACTIVE --
-- (rollback could cause phantom owner) --EXEC %%ObjectSchema (Name = @oldowner).Lock(Exclusive = 0) -- should succeed due to object lock above
-- SHARE LOCK NEW SCHEMA --
EXEC %%ObjectSchema (Name = @newowner).Lock(Exclusive = 0) -- may fail, check below anyway-- RESOLVE NEW OWNER NAME (ATTEMPT ADDING IMPLICIT ROW FOR NT NAME) --
-- Disallow aliases, and public cannot own objects --if @@error = 0 -- lock success, indicate new owner may exist, verify further
select @newuid = schema_id from sys.schemas where name = @newowner
and schema_id not in (3,4) -- INFORMATION_SCHEMA, sys
if @newuid is null -- indicate lock failed
begin EXEC @ret = sys.sp_MSadduser_implicit_ntlogin @newowner if (@ret = 0) -- successselect @newuid = schema_id from sys.schemas where name = @newowner
-- Member locked by sp_MSadduser_implicit_ntlogin endif @newuid is null OR
-- the schema name and its owner name must be the same
not exists (select ss.name
from sys.schemas ssjoin sys.database_principals su on ss.principal_id = su.principal_id
where ss.name = @newowner and ss.name = su.name)
begin-- Implicit login added above is not rolled back
-- This is same as SQL 2000
COMMIT TRANSACTION
raiserror(15411, -1, -1, @newowner) return (1) endselect @stmtS = 'ALTER SCHEMA '
select @stmtS = @stmtS + quotename(@newowner)select @stmtS = @stmtS + ' TRANSFER '
if parsename(@objname, 2) is not null
select @stmtS = @stmtS + quotename(parsename(@objname, 2)) + '.'
select @stmtS = @stmtS + quotename(parsename(@objname, 1)) exec (@stmtS) IF @@ERROR <> 0 BEGIN-- Nested transaction is used by alter schema statement
COMMIT TRANSACTION
return (1) ENDCOMMIT TRANSACTION
-- WARNING AFTER THE OWNER TRANSFER --
raiserror(15477,-1,-1) return (0) -- sp_changeobjectownerGO
通過分析存盤程序，我們發現在兩個地方可能拋出Msg 15001的錯誤資訊，如下所示
if (@objid is null) OR
(select parent_obj from sysobjects where id = @objid) <> 0 OR
ObjectProperty(@objid, 'IsSystemTable') = 1 OR
parsename(@objname, 3) is not null OR
parsename(@objname, 4) is not null OR
exists (select * from sys.objects where object_id = @objid and schema_id in (3,4)) OR -- INFORMATION_SCHEMA, sys
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
EXISTS (SELECT * FROM sysdepends d WHERE
d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id -- that isn't a self-reference (self-references don't use object name)
AND @objid <> -- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
)
begin-- OBJECT NOT FOUND
COMMIT TRANSACTION
raiserror(15001,-1,-1,@objname) return 1end
-- object's schema name must be the same as the schema owner's name
if not exists (select so.name
from sys.objects sojoin sys.schemas ss on so.schema_id = ss.schema_id
join sys.database_principals su on ss.principal_id = su.principal_id
where object_id = @objid and so.principal_id is null and ss.name = su.name)
begin-- OBJECT NOT FOUND
COMMIT TRANSACTION
raiserror(15001,-1,-1,@objname) return 1end
然后我們分析發現在第二個SQL會因為不滿足條件，拋出錯誤資訊，具體分析截圖所示
其實從腳本的注釋資訊“object's schema name must be the same as the schema owner's name”，我們已經知道，這個腳本有個邏輯，判斷物件的schema name和schema owner's name是否一致，如果不一致的話，就會拋出錯誤資訊，而這里遇到這個問題是因為我們下面腳本恰恰修改了klb這個schema的schema owner,所以才導致了這個錯誤出現，
USE AdventureWorks2014;GOALTER AUTHORIZATION ON SCHEMA::[klb] TO [dbo];
這里將klb的schema owner修改回來，然后執行sp_changeobjectowner就會OK了，
USE AdventureWorks2014;GOALTER AUTHORIZATION ON SCHEMA::[klb] TO [klb];
GOUSE AdventureWorks2014;GOEXEC sp_changeobjectowner 'klb.[k3]', 'dbo';
GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/263699.html
標籤：SQL Server
下一篇：Power BI成功的背后