Simplifies Systems Using Schemas
Schemas introduced in SQL Server 2005 allow Tables, Stored Procedures and Views and other design objects to be
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.
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
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.