Tuesday, April 26, 2011

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

No comments: