"Cannot find the object" error when modifying schema for existing table

Question

I am trying to alter my table schema from MySchema.TableName to dbo.TableName using the following command -

ALTER SCHEMA MySchema TRANSFER dbo.TableName

I get the following error -

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'TableName', because it does not exist or you do not have permission.

By searching I also added the following commands to no avail.

ALTER AUTHORIZATION ON SCHEMA::MySchema to dbo;
ALTER AUTHORIZATION ON OBJECT::MySchema.TableName TO SCHEMA OWNER;
ALTER SCHEMA MySchema TRANSFER dbo.TableName

I keep getting the same error.

My table name is correct and so is the database where I am running. Any help?

possible duplicate of [How do I change db schema to dbo](http://stackoverflow.com/questions/1146599/how-do-i-change-db-schema-to-dbo) — alroc, May 11 '14 at 03:50
@alroc: I respectfully disagree that this is a duplicate. The linked question simply asks how to move tables to another schema. This question is about a specific error that came up when trying to move a table to another schema incorrectly. — J0e3gan, May 11 '14 at 04:15
By following the instructions in that answer, you'll get the table moved properly. It's the same root question: "what's the right way to move tables between schemas?" — alroc, May 11 '14 at 10:35

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You have the schemas backwards.

Change...

ALTER SCHEMA MySchema TRANSFER dbo.TableName

...to:

ALTER SCHEMA dbo TRANSFER MySchema.TableName

See a related SO answer for confirmation where the source and target schemas should be in your ALTER SCHEMA statement.

If the ALTER SCHEMA statement still does not work, then you have another issue (e.g. you don't have necessary permissions).

For any permissions issues you may bump into, refer to TechNet's ALTER SCHEMA reference, specifically its Permissions section, which says:

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.

If the securable has an EXECUTE AS OWNER specification on it and the owner is set to SCHEMA OWNER, the user must also have IMPERSONATION permission on the owner of the target schema.

All permissions associated with the securable that is being transferred are dropped when it is moved.

yup, I just realized that. :) – VKarthik May 11 '14 at 03:58 — VKarthik, May 11 '14 at 03:58

"Cannot find the object" error when modifying schema for existing table

1 Answers1