Watch those Schema's

One of the new features of SQL 2005 is the use of Schemas. These can be perceived of as adding value to the life of a DBA in various ways including the following two:-

One view of Schemas

One of the benefits derived from them is the ability to group (securable) objects together to facilitate ease of security management (allocation of permissions). The other end of the security management link is the use of principles (pre-authorised groupings of resources).

For example

A principle (Salesperson1) who belongs to a higher principle (SalesRole) can have select permission on all tables within the schema (SalesSchema), one of which is 'Sale' by the one entry in the system tables corresponding to the instruction

GRANT SELECT ON SCHEMA::SalesSchema TO SalesRole

This security permission can be exercised either by referring to the object name using the full schema path for the object

SELECT * FROM SalesSchema.Sale

or by ensuring that the principle is associated with the schema as part of its specification.


View two of schemas

Another benefit of them is that within a schema we don't have to worry about the order in which objects are created, they are checked as a group after their creation rather than individually. Do I hear a cheer from all those case tool users who have been affected by the errors caused by bad ordering of automatically generated TSQL, no, then maybe you should find out about case tools, they have many good aspects as well.

However, be careful about this one feature within SQL Server. Because although it is the case that all objects created within a schema at the time of schema creation, are checked as a group. There is no concept of dropping a group of objects belonging to a schema by dropping the schema itself. You must drop all objects within the schema first. Otherwise you will get an error message.

So ensure that you create the tables in the order Parent -> Child -> NextChild -> etc in your scripts, even if they are being created within a schema. Then if you need to remove them at a later date, using a dynamically generated script, you can order them correctly using their id.