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
andfn_my_permissions
. Could not find anything useful.
What am I missing?