Contained databases

Contained databases are isolated from other databases and from the instance of SQL Server that hosts the database.

By having a contained database and contained users, multiple database instances of Synergetic can be installed on the same SQL instance, each of which contains data that will not be accessible to users of other databases.

Following the model of contained databases, all Synergetic applications connect to the single database as contained users. Credentials for each user are stored in the Syndatabases table and username, role and password details can be accessed by accessing the view from the specified database.

The Syndatabases table contains the following columns:

• standard schemas (dbo, media, finance)
• any additional schemas
• role required for SQL users to be able to access the finance or additional finance schemas
• credentials the applications uses to connect (username and password)
• database version including the installation data.

Connection process

When a user authenticates with Synergetic or SynWeb the following connection process is completed:

1. Connection to the database is established using the user credentials.
2. Check is performed to determine whether the user has permission to access the finance schema.
3. Permission to access the finance schema is granted, if the user has sufficient role permissions. See Maintaining public finance role security access in the single database structure.
4. Application switches its connection to the credentials stored in pvSyndatabases for every subsequent call.
5. Application switches its connection to utilise the relevant finance user stored in pvSyndatabases, if permission to access the finance schema was granted.