SQL Server 2016 - Moving table across schemas - Error: Cannot transfer the object

Question

Before posting this, I searched both on Stack Overflow and the web in general.

My task is simple: I would like to move table A from schema X to schema Y. When trying it out like this:

ALTER SCHEMA Y TRANSFER X.A

I get the following error:

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

• I checked multiple times that both the schemas and the table exist
• Environment: SQL Server 2016
• Table A was copied over from a different database on a different server, using SQL Server Export/Import wizard
• I have similar permissions on both schemas
• All permissions are inherited via an Active Directory group
• Earlier in the day, I executed the same command for a different table and it worked perfectly fine.
• Table A contains nearly 280 million rows. So copying via temp table or table scripting is not efficient
• The MSDN article mentions that "To transfer a securable from another schema, the current user must have CONTROL permission on the securable (not schema) and ALTER permission on the target schema"
  https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15

• I am not sure how to check for CONTROL permission. I tried sp_table_privileges and fn_my_permissions. Could not find anything useful.

What am I missing?

Answer 1

Who owns the table? eg

select s.name schema_name, 
       t.name table_name, 
       user_name(coalesce(t.principal_id,s.principal_id)) owner_name
from sys.tables t
join sys.schemas s 
  on t.schema_id = s.schema_id

You check your permissions with

select *
from sys.fn_my_permissions('X.A','OBJECT')