This is one of those terms in the IT world that actually has several definitions depending on who you ask. For a developer, a schema is usually meant as the structure or the definition of an object. For SQL Server as an example, the schema would be how a table was defined. How many columns were in the table, the data types of each column, and any possible constraints on the table.
However, for a database administrator, the term schema means something else entirely. It is actually a unit of organization and security. Schemas would be used to provide logical separation of tables, views, or procedures based on department, location, or use. Schemas were introduced as an actual object in SQL Server 2005 not only for organizational purposes but to provide an extra level of security. Instead of having to manage permissions at each table, view, or stored procedure, administrators could place permissions at the schema level to provide or deny access to those objects underneath that schema.
I have two other posts about schemas, one on Creating and Managing Schemas and another on how schemas are used in the four-part name of objects. I do plan on adding a series next winter on SQL Server Security post in the future where I will demonstrate how to apply permissions to security. (I will try to update this post at that time.)
Be the first to comment on "What is a Schema?"