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
Last activity: Aug 25, 2025, 09:04 PM