In a previous post we discussed the four part name of a SQL Server object in 2005 and later that included the SCHEMA as part of its name.
There are many advantages to using schemas, but mainly to allow better and more flexible organization of objects as well as allowing for an additional security boundary. In the example above we are creating a new schema named Sales and allowing the Bjorn identity to be the owner of the Sales schema. Next we GRANT the SELECT permission to Jane and then DENY the SELECT permission from Sara. This will Grant or Deny the SELECT permission to ALL of the tables and views that are being organized under the Sales schema.
Another way of creating schemas is to streamline the creation while also creating a table the will be stored underneath that schema. In this following example, we create the Production schema while also creating a simple Products table.
Another issue we may run into is if we want to move a table from one schema to another. Normally, this is not necessary if we started with good database design. But in case a table needs to be moved it actually needs to be done as part of an ALTER SCHEMA statement. In this example, we will alter the HR schema and then TRANSFER the TestTable from the dbo schema into the HR schema.
Finally, if you no longer want to use a schema you can use the DROP SCHEMA statement. However, you need to make sure you transfer all the objects to another schema first.