Schemas introduced in SQL Server 2005 allow Tables, Stored Procedures and Views and other design objects to be grouped together.

MUST makes it easy to map your databases accross multiple schemas, with schemas being automatically created, tables being created on the schemas and synonyms automatically created for all table objects.

The product also has a unique ability to completly re-map all SQL when creating Views and Stored Procedures, to any desired schema.

Schemas allow a large application to be partitioned or split into logical chunks, this makes it easier to manage the application. It also allows one or more Access databases to be split across multiple schemas in a single SQL Server database.

dbo, The Default Schema

You are always using a schema when working with SQL Server 2005/2008 and if not otherwise specified this is the dbo (database owner) schema. In SQL Server 2005/2008 the idea of ownership was extended to resolve problems associated with object owners being removed from a database.

You can add to the list of schemas, to create schemas appropriate to your application for example “Sales”, “Accounts”, “Forecasting” etc.

Synonyms

In order that you can maintain compatability when converting existing SQL, and to simplify working with schemas, it is useful to have synonyms created for each table in a schema.

When working with the default schema (which is normally dbo), you do not need to prefix tables with the schema name. For example tblSales does not need to be refered to as dbo.tblSales. But if the table was in the Sales schema, then you would have to refer to the table as Sales.tblSales.

To get around this problem, we create a synonym “tblSales” which becomes a short-hand for saying “Sales.tblSales”.

MUST allows you to define schemas, set a default schema for each database, fine tune individual tables to be in appropriate schemas and automatically create the required synonyms for each table.

Security, Schemas And Database Roles

One of the simplest methods to manage security, is to create “database roles” for your application, and then assign users to one or more of the available roles.

For example, you create two roles, one called “ReadOnlyUsers” and the other called “DataUpdateUsers”. Each role can then be granted specific permissions on the schemas, and so in turn to all objects within the schema.

MUST will allow you to create the database roles and grant appropriate permissions on each schema.