When SQL Server 2005 finally arrived, I suspect that we were quite wrong about the most important features in a radically-altered product. CLR, and XML support, for example, proved to be less significant than we thought. On the other hand, we all assumed that schemas were little-more than a tidying-up exercise for access control, making a 'user' into a 'schema' so as to comply with the SQL standard. It was a confusing term, since we were used to calling the design of the database the 'schema'. We're referring here to the security or namespace domain that contains database objects. With schema, SSMS didn't really help much to portray the new hierarchical design, since it kept rigidly to the old 'Enterprise Manager' visual 'browser' model. Schemas appeared, mysteriously, below the object level whereas the hierarchy actually goes from server to database, thence, via Schemas, finally to objects.

Schemas are ideal for creating application interfaces. We can use them to group all those objects that are to be used for a particular job role, such as a department, function or division within a company. We assign the job role to a database role that has permission to access the relevant schema and therefore all the objects that are within. In this way, schemas made access-control security much simpler, and easier to maintain. We can even script them separately, and store them in source-control with the application.

From SQL 2012 onwards, the combination of schemas and windows groups even make it possible to do the administration of security in one place, the Active Directory. The admin can add and remove individual users from the group and they are automatically assigned, or revoked, permissions to their default schema.

Schemas make development simpler, since it becomes possible to make each schema almost like a separate database, with its own users. We can script out each schema separately and, with proper use of interfaces between schemas, even maintain each schema at a different version without too much risk of breaking anything.

All this complexity seems irrelevant to the smaller database, where it is no trouble to assign permissions to individual objects. AdventureWorks wouldn't lose much from having no schemas other than dbo, and the information_schema. It is different, though, for a hard working corporate database that is being actively developed and used by several applications, with perhaps many thousands of tables, views, functions and procedures that must be assigned the correct permission. Without a schema-based security model, this is likely to become extremely hard work.

Phil Factor (Guest Editor)