What is a Schema?
In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.
The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principle and form a single namespace.
All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principles own schemas.
A schema can be owned by either a primary or secondary principle, with the term “principle” meaning any SQL Server entity that can access securable objects.
Principle types that can own schemas:
- Primary
- SQL Server Login
- Database User
- Windows Login
- Secondary
- SQL Server Roles
- Windows Groups
- Default Schemas
Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.
——————————————————————————–
SQL Server 2005 Schema Objects
- Tables
- Indexes
- Views
- Synonyms
- Stored Procedures
- Triggers
- Functions
- Assemblies
- Types
- Defaults

Thanks!! This makes the purpose of schema in sql server 2005+ very clear.
thanks it is very useful…
Thanks …it’s very clear……….
its really nice article thnx a lot ….its easy to understand wht is schema…it is really helpful…
Commemorate answer…….Thanks!!!!!!!!
really helpful
Good.
Simple and very clear, keep this up
GOOD AND CLEAR
It’s very nice and clear and I need transaction management information also.
plz provide article on this topic also
Hi, I found your website @ yahoo and i’ve read a few of your different posts. I love what u post! I just added you to my Google Information Reader
. Keep up the great work . Stay up for see more from you within the future.
Thank you for the kind feedback.
thankyou for explian schemas
i prefer it so much
simple ‘n’ good
Good and try to write these sort of articles more which will help us alot.
Simple & elegant ….
Thanks really meaningful..
very helpful , Thanks!
Thanx.
this is very usefull and very simple way… thanx a lot..
This is useful tip thanks..