What is a Schema in SQL Server 2005?

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 principal 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 principals own schemas.

A schema can be owned by either a primary or secondary principal, with the term “principal” 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

83 Responses to What is a Schema in SQL Server 2005?

  1. waseem April 8, 2014 at 7:30 am #

    informative article very well explained.

  2. kolimisteenused June 8, 2013 at 8:37 pm #

    When some one searches for his required thing, so he/she needs to be
    available that in detail, thus that thing
    is maintained over here.

  3. live hotmail June 1, 2013 at 5:00 am #

    Great beat ! I would like to apprentice whilst you amend your site, how could
    i subscribe for a blog web site? The account helped me a
    acceptable deal. I were tiny bit familiar of this your broadcast offered vivid clear concept

  4. Ian McKay November 7, 2012 at 6:44 am #

    Nice article..but the word ‘principle’ as in ‘security principle’ (i.e. an entity that be authorised to access a device) should be spelled ‘principal’.

    • Michael Ritacco November 8, 2012 at 12:35 am #

      Fixed. Thanks for catching that.

  5. Rajan October 19, 2012 at 6:21 am #

    I got benifited by your article .. thank a lot.

  6. Rupesh Mondal July 4, 2012 at 4:26 am #

    Nicely Explained.

  7. swati May 24, 2012 at 5:20 pm #

    thanks it’s usefull for me

  8. Anirudh May 19, 2012 at 11:17 am #

    Great Job !! Very Useful Notes

  9. Niharika April 27, 2012 at 7:59 am #

    Nice

  10. Bholanath S Kanaujia March 16, 2012 at 4:01 am #

    Its really good for basic idea about schema……….

  11. M. Sandal July 15, 2011 at 2:57 am #

    This is useful tip thanks..

  12. Mahender July 15, 2011 at 2:52 am #

    Thanx.

    this is very usefull and very simple way… thanx a lot..

  13. Vikrant Sharma May 25, 2011 at 11:49 pm #

    very helpful , Thanks!

  14. mayur patil April 27, 2011 at 11:11 pm #

    Thanks really meaningful..

  15. Dhamo April 23, 2011 at 10:08 am #

    Simple & elegant ….

  16. RAjKumar U April 18, 2011 at 11:25 pm #

    Good and try to write these sort of articles more which will help us alot. :)

  17. rajesh chauhan April 16, 2011 at 3:29 am #

    simple ‘n’ good

  18. Avanish kumar March 10, 2011 at 11:31 pm #

    i prefer it so much

  19. suresh January 28, 2011 at 1:20 am #

    thankyou for explian schemas

  20. Ardath Reinwald January 17, 2011 at 6:11 pm #

    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.

    • Michael Ritacco January 17, 2011 at 9:28 pm #

      Thank you for the kind feedback.

  21. Naresh October 26, 2010 at 9:44 pm #

    plz provide article on this topic also

  22. Naresh October 26, 2010 at 9:43 pm #

    It’s very nice and clear and I need transaction management information also.

  23. JAADU October 3, 2010 at 3:18 am #

    GOOD AND CLEAR

  24. claude September 1, 2010 at 12:50 am #

    Simple and very clear, keep this up

  25. Anuradha July 2, 2010 at 5:13 am #

    Commemorate answer…….Thanks!!!!!!!!

  26. kinjal panchal June 10, 2010 at 7:10 am #

    its really nice article thnx a lot ….its easy to understand wht is schema…it is really helpful…

  27. Koteswar Rao June 3, 2010 at 8:01 am #

    Thanks …it’s very clear……….

  28. josphine May 31, 2010 at 4:42 am #

    thanks it is very useful…

  29. Rashmi June 12, 2009 at 12:25 pm #

    Thanks!! This makes the purpose of schema in sql server 2005+ very clear.

Powered By Mekhano Media