Menu Request Demo

Schema Support in SQL Server 2005+

08 January 2009

Schemas were introduced into SQL 2005 to provide security containers for objects (eg tables and views). They allow DBAs to manage security at the schema level - any objects created in a schema inherits the schema's security properties.

Just as usefully schemas also provide a nice meta-data container for objects so that you can use a schema to group objects together - for example to relate a set of objects together relating to a specific department or function.

WhereScape RED creates and manages all of its objects in the dbo schema - this ensures that no issues (naming or security) are introduced when managing or updating the database objects due to schemas. In order to make use of schemas for security and grouping over user tables and views WhereScape consultants have released a procedure that manages synonyms within schemas to point back to the dbo objects. This means that users can be shut out of the dbo schema and restricted to see only the objects within the schemas that they have access to.

For example we can now have a project called Sales (in the Schema project group) that contains fact_sales, dim_date and dim_customer. Running this procedure will create a Sales schema in SQL, synonyms in that schema pointing to fact_sales, dim_date and dim_customer, and optionally a Sales SQL Server user limited to the Sales schema. As with projects the same object can happily exist in more than one schema.

The procedure (admin_schema_maintenance) is available by emailing support. When it is run or processed within RED it manages schemas and synonyms (and optionally users to access those schemas) based on RED's projects. The script works by creating SQL Server schemas with the same name as RED Projects that sit under a Project Group called Schema (you need to create this group and projects). The procedure will create synonyms for any Dimensions, Views or Fact tables that exist in these projects in those schemas. The following parameters control procedure options:
SCHEMA_MAINTAIN_SCHEMAS - 0 or 1 will drop any existing schemas (except dbo) and recreate them
SCHEMA_MAINTAIN_USERS - 0 or 1 will drop and recreate users with access only to their schema
SCHEMA_PRINT_ONLY - 0 or 1 will only print the generated SQL not execute it

The procedure can be added to RED by creating a new Procedure called admin_schema_maintenance and then pasting in the procedure code. The procedure is not part of RED and can be added, changed, run and scheduled as required. We would recommend that the script be tested in your environment prior to running in production.

NOTE: Do not forget that synonyms are dependent objects in SQL Server - if they interfere with development then drop the synonyms for those objects being recreated - or drop all synonyms during development. To do this:

  • An easy way of removing synonyms for a single object is to move that object out of the Schema project(s) and rerun the procedure.
  • A simple way of removing all synonyms quickly is to rename the Schema project group (eg Schema_Off) and rerun the procedure.

Considerations should be made for migrating objects to production. It may be easier to drop all synonyms in production first by renaming the Schema project (as above), run procedure, migrate objects over, rename Group back to Schema and rerun procedure.


Comments 0

Leave a Comment

No comments.