ALTER SCHEMA (Transact-SQL)

Transfers a securable between schemas.

Topic link iconTransact-SQL Syntax Conventions

Syntax

ALTER SCHEMA schema_name TRANSFER securable_name

Arguments

  • schema_name
    Is the name of a schema in the current database, into which the securable will be moved. Cannot be SYS or INFORMATION_SCHEMA.
  • securable_name
    Is the one-part or two-part name of a schema-contained securable to be moved into the schema.

Remarks

In SQL Server 2005, users and schemas are completely separate. For more information, see User-Schema Separation.

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.

Warning

In SQL Server 2005 the behavior of schemas is changed from the behavior in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that is introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL).

Permissions

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.

Examples

The following example modifies the schema HumanResources by transferring the table Address from schema Person into the schema.

USE AdventureWorks;
GO
ALTER SCHEMA HumanResources TRANSFER Person.Address;
GO

See Also

Reference

CREATE SCHEMA (Transact-SQL)
DROP SCHEMA (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

User-Schema Separation

Help and Information

Getting SQL Server 2005 Assistance