Users and schemas are completely separate. The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users;A schema is simply a container of objects. Schemas own objects and principles own schemas.
- A single schema can contain objects owned by multiple database users.
- A database user can be dropped without dropping objects in a corresponding schema
A schema can be owned by any user, and its ownership is transferable.
Why Schema introduced on SQL Server?
The Users and Schemas separation means objects and schemas can be created before users are added to the database. It also means a user can be dropped without specifically dropping the objects owned by that user. A schema can only be owned by one user at a time, but a single user can simultaneously own many schemas.
How to transfer Schema ownership?
The following example transfers ownership of the schema LabProduction to user Sivaprasad.
ALTER AUTHORIZATION ON SCHEMA::LabProduction TO Sivaprasad;
Changes the ownership of a securable – using ALTER AUTHORIZATION
ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner. Ownership of database-contained entities can be transferred to any database-level principal. Ownership of server-level entities can be transferred only to server-level principals
How to move objects between Schema?
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.
For example, below modifies the schema HumanResources by transferring the table Address from schema Person into the schema
ALTER SCHEMA HumanResources TRANSFER Person.Address;
-- Create TestDB Database
-- Change the DB Context to TestDB
-- Created Schema Student
--– Created table in Student schema –
--– Assign Permission to Schema