Alter Schema Transfer fails even when I'm a db-owner

Question

I asked this on GitHub here, but I think we can turn into a more generic question to get some helpful ideas faster. First, I ran the script here, which shows that I am a member of the db-owner group.

This is on the install script for an older tool called AutoEdit. Despite the issue below, I'm able to turn on AutoAudit for specific tables and it's working okay.

The supplied script stores a few stored proc under a schema with my username, then tries to transfer them to the "audit" schema later. The AutoEdit concepts and stored procs are all actually working, but now I need to clean it up and put in another environment.

Error is:

Cannot find the object 'pAutoAudit', because it does not exist or you do not have permission.

It created the stored proc as

"Corp\myuserid.pAutoAudit"

I added two print statements to help debug:

-- 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)

Above shows:

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

The schema Audit exists, and it has one stored proc in it: pAutoAuditArchive.

I have added a related question here: SQL Server setting that changes schema from dbo

Answer 1

To fix this specific problem, I saw the correct syntax from the Lauren answer here, like this:

ALTER SCHEMA NewSchema TRANSFER [OldSchema].[TableName]

The following script temporarily fixes the issue. I have to study more how to fix the original script if indeed it has an error:

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) 

However, to fix the "AutoEdit" script referenced in the question, the problem can be corected by adding this code to AutoEdit or running it before AutoEdit. Apparently, it assumes you are in the "dbo" default_schema. You might have to set the value back to the original value if needed after the script.

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

The better fix for when I moved it to another environment, was to change all the "Create Proc xxxx" to "Create Proc dbo.xxxx".