Schema in SQL Server

 
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.

1. A single schema can contain objects owned by multiple database users.
2. 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;
GO

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;
GO

-- Create TestDB Database Create database TestDB go -- Change the DB Context to TestDB Use TestDB go -- Created Schema Student Create Schema Student go --– Created table in Student schema – Create Table Student.Student_Info ( Student_No int Primary Key identity(1,1), Student_Name varchar(50) ) go --– Insert Data Insert Into Student.Student_Info Values('Sivaprasad') go --– Select Data Select * From Student.Student_Info go --– Create another schema Course Create Schema Course go --– Transfer Objects from Student Schema to Course Schema ALTER SCHEMA Course TRANSFER Student.Student_Info go --– Assign Permission to Schema GRANT SELECT ON SCHEMA::Course TO Sivaprasad go