2

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?

0

1 Answer 1

0

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')
3
  • 1) Owner of the table is dbo - just like all other tables in that database. 2) At the table level, I've the following permissions: SELECT, UPDATE, REFERENCES, INSERT, DELETE, VIEW DEFINITION, ALTER. This seems to be the same for all other tables.
    – Venkat
    Commented Jul 28, 2021 at 6:57
  • You need to have an ALTER ANY SCHEMA privilege to do so and also a CONTROL over the objects.
    – SQLpro
    Commented Jul 28, 2021 at 11:15
  • You only need ALTER on the target schema. Commented Jul 28, 2021 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.