Schemas allow you to better manage large applications or consolidate together multiple databases.
MUST creates synonyms in SQL Server for any tables in a schema which allow you to take advantage of
schemas without the need to make changes in your SQL.
For example a table in the sales schema called Sales.Orders will have the synonym Orders
created; because otherwise you would need to change any converted SQL to
refer to the table using the full schema prefix; which would be messy.
Schemas and Synonyms go hand in hand together.
Schemas
Schemas should be decide on as early as possible, if entered before Analysis commences then
when picking the Access databases each can be assigned a default schema.
| schemas |
Use this icon in the options menu to create a list of schema names. |
| Access databases |
When selecting databases using the 'Access databases icon' in the migrate structure and data' menu
each database can be assigned a default schema, in which case all tables in that database get assigned the schema name. |
| Browse Tables |
The 'Browse Tables' icon in the 'data dictionary' menu can then be used to selectively assign
tables into particular schemas, thus grouping the tables together. |
Schemas and security
| create database roles |
The create database roles icon, allows special database roles to be created and each role can be
assigned distictive permissions on the schemas. |
| manage users |
Whilst you are encouraged to use the 'SQL Server management studio' to manage permissions, an icon
is provided to create users, these users are then assigned a database role in the database. |
When converting SQL, MUST generates a parameter table for reporting, because read-only users
will still need to update report parameters, we place this on a distictive schema called
Reporting. This is an example of anticipating security needs, and for example two database roles
could be setup. One for read-only users granting INSERT, UPDATE and DELETE on the Reporting schema
(which they do through a view, so they can not change anyone elses data) and SELECT permissions
on all other schemas. Where as the standard user role could also be granted INSERT, UPDATE and DELETE
permissions on the other schemas in addition to the reporting schema.