Sample Header Ad - 728x90

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

3 votes
1 answer
1706 views
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?
Asked by Venkat (131 rep)
Jul 27, 2021, 04:30 PM
Last activity: Aug 25, 2025, 09:04 PM