即使我是数据库所有者，更改架构传输也会失败

Question

我在GitHub上问过这个问题，但我认为我们可以把它变成一个更通用的问题，以便更快地获得一些有用的想法。首先，我在这里运行脚本，它显示我是db-owner组的成员。

这是在名为“自动编辑”的旧工具的安装脚本上。尽管存在以下问题，但我能够为特定表启用自动审核，并且它工作正常。

提供的脚本将一些存储的proc存储在带有我的用户名的模式下，然后稍后尝试将它们传输到“审计”模式。自动编辑概念和存储的过程实际上都在工作，但现在我需要清理它并将其放入另一个环境中。

错误是:

找不到对象“pAutoAudit”，因为它不存在或者您没有权限。

它将存储的过程创建为

“公司\myuserid.pAutoAudit”

我添加了两条打印语句来帮助调试:

-- This is the line of code (the EXEC below) that is causing the issue:
print Concat('@AuditSchema=', @AuditSchema)
SET @Sql = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER dbo.pAutoAudit'
print Concat('@Sql=', @Sql)
EXEC (@Sql)

以上显示:

@AuditSchema=Audit
@Sql=ALTER SCHEMA [Audit] TRANSFER dbo.pAutoAudit

模式Audit存在，其中有一个存储过程：pAutoAuditArchive。

我在这里添加了一个相关的问题：从dbo更改架构的SQL服务器设置

Answer 1

为了解决这个特定问题，我在这里看到了Lauren答案中的正确语法，如下所示：

更改架构新架构转移[旧架构]。[表名]

以下脚本暂时修复了该问题。如果原始脚本确实存在错误，我必须进一步研究如何修复它：

use myDbname 
Declare @AuditSchema varchar(32) 
Declare @OldSchema varchar(32) 
Declare @SQL varchar(max) 

Set @AuditSchema = 'Audit' 
Set @OldSchema = 'Corp\myuser' 
-- ALTER SCHEMA NewSchema TRANSFER [OldSchema].[TableName]


SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAudit'
print Concat('@SQL=', @SQL) 
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditAll'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditDrop'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditDropAll'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditRebuild'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditRebuildAll'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditSetTriggerState'
EXEC (@SQL)

SET @SQL = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.pAutoAuditSetTriggerStateAll'
EXEC (@SQL) 

但是，要修复问题中引用的“自动编辑”脚本，可以通过将此代码添加到自动编辑或在自动编辑之前运行它来解决问题。显然，它假设您处于“dbo”default_schema。如果需要，您可能需要在脚本之后将值设置回原始值。

DECLARE @SQL2 VARCHAR(max)
SET @SQL2 = 'ALTER USER ' + quotename(current_user)  + ' WITH DEFAULT_SCHEMA = dbo' 
print Concat('@SQL2=', @SQL2) 
exec (@SQL2)

当我将它移动到另一个环境时，更好的解决方法是将所有“创建程序xxxx”更改为“创建程序dbo.xxxx”。