In this article we will show you how to significantly simplify your SQL Server design and produce a system which can be more easily maintained, and a security model which is simple to administrate. All the features described here are fully implemented in MUST which generates schemas, database roles, synonyms and can be used to split groups of tables into schemas.

Schemas

You may think that you don’t already use schemas, but you do. When you create objects in SQL Server they are allocated to a schema, and the default schema is normally dbo the database owner account. So a table called Sales, can also be referenced as dbo.Sales. You are encouraged when creating objects to always explicitly specify the schema name, but if not specified then your default will apply.

A schema is simply a name (namespace) which can be used to group together SQL Server objects.

Below you can see how we could take several Access databases and move all the tables into separate schemas, or we could take a single Access database and split the tables into groups, placing each group in a separate schema.

Referential integrity can also be added across schemas, for example allowing for an order table in the sales schema to have a referential integrity constraint to the customer table in the client schema.

Below we see an example of setting up a set of schemas.

Next we see how Northwind is prepared to analyse all the tables into one of the schemas.

Finally we fine tune our selections to place some of the tables on alternative schemas.

The following extract from a pre-migration script file creates the schemas and shows an example table:-

CREATE SCHEMA [General] AUTHORIZATION [DBO]
GO
CREATE SCHEMA [Sales] AUTHORIZATION [DBO]
GO
CREATE TABLE [Sales].[Categories] (
[CategoryID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
[CategoryName] VARCHAR(15) NOT NULL ,
[Description] VARCHAR(MAX) NULL,
[Picture] VARBINARY(MAX) NULL
)

Top of Page

Synonyms

Once a table is placed on a schema (other than the default schema, normally dbo) you must use the schema prefix when referring to the table.

For example to select from the categories table now in the general schema, we must use the following.

SELECT * FROM [general].[categories]

Apart from this making your SQL a bit longer, it can also cause problems when you come to convert SQL in Access to SQL Server. All these problems will go away when you introduce synonym’s.

A synonym is simply an alternative name for referring to an object. So if we create the following synonym.

CREATE SYNONYM [Categories] FOR [Sales].[Categories]

Our SQL can now be changed to read.

SELECT * FROM [order]

So for every table in a schema, it can be a good strategy to create a synonym.

Top of Page

More on schemas

Schemas can be created for other purposes. For example where are you going to put all your views and stored procedures? You could put them all on the default dbo schema, or maybe you split them onto the appropriate schema. For example placing a view vw_sales on the sales schema.

You do need to watch that splitting up all the views onto different schemas does not create a maintenance by having everything split-up. This all comes down to your own experiences and judgement on how to best manage the code.

Top of Page

Database role security

Closely related to the subject of schemas, is database roles. If you start by creating a set of database roles for your application then this can be used to simplify managing security.

We will create two roles, one for read-only users, and the other for users who can maintain data.

CREATE ROLE [AppMaintainData] AUTHORIZATION [dbo]

CREATE ROLE [AppReadOnly] AUTHORIZATION [dbo]

Now consider having a number of tables on the sales schema which must be available to both roles, but read only for the AppReadOnly role.

GRANT SELECT,INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Sales] TO [AppMaintainData]

GRANT SELECT,EXECUTE ON SCHEMA:: [Sales] TO [AppReadOnly]

The EXECUTE permissions will allow stored procedures on the schemas to be executed and may not be required in the application.

Now consider having a Reporting schema, which we want all users to be able to change data, because it is used for reporting.

GRANT SELECT,INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Reporting] TO [AppMaintainData]

GRANT SELECT,INSERT, UPDATE, DELETE,EXECUTE ON SCHEMA:: [Reporting] TO [AppReadOnly]





Top of Page

Managing users

When you need to manage a new user in your system you could then follow the following steps. Add the user to the SQL Server login, and map the user into your database then select one of the AppMaintainData or AppReadOnly database roles available to the user.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz