I have a very simple question:
How to change DB Schema from dbo to another schema?
I have already tried:
alter schema [new_schema] transfer [old_schema].[object_name];
but I get an error about permission while using the query and the error property can not be modified when using GUI. I created the database myself and I have got all the permissions. The only problem I can think is from GoDaddy because I am using GoDaddy's plan to host my Database. I can successfully transfer a table from any other Schema to dbo but I can not transfer any table from dbo to any other schema...
Error:
'Cannot transfer the object 'Production', because it does not exist or you do not have permission.'
Any help will be appreciated.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
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.
ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.
If a one-part name is used for securable_name, the name-resolution rules currently in effect will be used to locate the securable.
All permissions associated with the securable will be dropped when the securable is moved to the new schema. If the owner of the securable has been explicitly set, the owner will remain unchanged. If the owner of the securable has been set to SCHEMA OWNER, the owner will remain SCHEMA OWNER; however, after the move SCHEMA OWNER will resolve to the owner of the new schema. The principal_id of the new owner will be NULL.
Maybe you should just script out the table you want to move and recreate it in the target dbo?