-
SQL Server执行sp_changeobjectowner遇到＂Msg 15001, Level 16,...＂错误探究
在SQL Server中使用sp_changeobjectowner存储过程修改对象所有者（Owner）时，可能会遇到下面错误信息，当然对象klb.[k3]这个表确实是存在的。如下所示：
注意：个人测试环境为SQL Server 2014的标准版，如有不同情况，请以实际环境为准。
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存储过程的定义如下：
通过分析存储过程，我们发现在两个地方可能抛出Msg 15001的错误信息，如下所示
然后我们分析发现在第二个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,所以才导致了这个错误出现。
这里将klb的schema owner修改回来，然后执行sp_changeobjectowner就会OK了。
注意：个人测试环境为SQL Server 2014的标准版，如有不同情况，请以实际环境为准。
USE AdventureWorks2014;
GO
EXEC 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
GO
create procedure sys.sp_changeobjectowner
@objname nvarchar(776), -- may be "[owner].[object]"
@newowner sysname -- must be entry from sysusers
as
Set 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_securityadmin
if (is_member('db_owner') = 0) and
(is_member('db_securityadmin') = 0 OR is_member('db_ddladmin') = 0)
begin
EXEC %%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
begin
EXEC %%System().AuditEvent(ID = 1094864724, Success = 1, TargetLoginName = NULL, TargetUserName = @newowner, Role = NULL, Object = @objname, Provider = NULL, Server = NULL)
end
if parsename(@objname, 1) is null
begin
raiserror(15253, -1, -1, @objname)
return (1)
end
BEGIN TRANSACTION
-- RESOLVE OBJECT NAME (CANNOT BE A CHILD OBJECT: TRIGGER/CONSTRAINT) --
select @objid = object_id(@objname, 'local')
if not (@objid is null)
begin
EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
if (@@error <> 0) -- lock failed
select @objid = null
end
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 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 so
join 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
end
select @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) -- success
select @newuid = schema_id from sys.schemas where name = @newowner
-- Member locked by sp_MSadduser_implicit_ntlogin
end
if @newuid is null OR
-- the schema name and its owner name must be the same
not exists (select ss.name
from sys.schemas ss
join 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)
end
select @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)
END
COMMIT TRANSACTION
-- WARNING AFTER THE OWNER TRANSFER --
raiserror(15477,-1,-1)
return (0) -- sp_changeobjectowner
GO
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 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 so
join 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
end
然后我们分析发现在第二个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;
GO
ALTER AUTHORIZATION ON SCHEMA::[klb] TO [dbo];
这里将klb的schema owner修改回来，然后执行sp_changeobjectowner就会OK了。
USE AdventureWorks2014;
GO
ALTER AUTHORIZATION ON SCHEMA::[klb] TO [klb];
GO
USE AdventureWorks2014;
GO
EXEC sp_changeobjectowner 'klb.[k3]', 'dbo';
GO 出处：https://www.cnblogs.com/kerrycode/p/14446167.html
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
SQL SERVER中递归
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
这是目前我见过最好的跨域解决方案！
减少回流与重绘
减少回流与重绘
如何使用KrpanoToolJS在浏览器切图
performance.now() 与 Date.now() 对比
一款纯 JS 实现的轻量化图片编辑器
关于开发 VS Code 插件遇到的 workbench.scm.
前端设计模式——观察者模式
前端设计模式——中介者模式
创建型-原型模式