Materialised Query – Setup and Controlling Connections
By default Materialised Query database are created on the same SQL Server as the Sharperlight System database. When a Sharperlight Service is started it scans the Server databases for any database that has a prefix of SLMQ_ When using Query Builder the Materialised Query Databases will appear in the Product List.
There are two ways to control the location and access to Materialised Query Databases
- Use SQL Server user mapping to control which Materialised Queries are accessible.
- Install the Materialised Query Datamodel so that the connection can to another Server
- Use Site Setup to specifiy which accounts have access
It is recommended that method 1 be used to set the Databases the Sharperlight account can have access to.
Using SQL Server User Mapping – METHOD 1
Before restricting the SQL Server account you notice it has access to two Materialised Query Databases Company A and B
In SQL Server Management Studio / Security / User Mapping restrict the User to just the desired Databases.
Ensure you give it db_owner to Sharperlight and the desired Materialised Query database SLMQ_***
In this example just Sharperlight and Company A are mapped
If Sharperlight Instances are being used then one should ensure that each Instance System Connection Account is restricted to it’s own Sharperlight database and Materialised Databases
Restart the Service and goto Query Builder and select the Products.
Notice the only Company A is visible and Company B is gone from the list.
Use MQSYSDM Datamodel to define connections – METHOD 2
On occasions you may wish to locate the Materialised Query Databases on a different Server. An Example of this would be where Sharperlight has multiple Instances and you need to isolate the Materialised Query Databases of one of the Instances. This is done by installing the Materialised Query Datamodel MQSYSDM.meta. Once installed you can then specifty the Server to use for Materialised Query Database in Client Setup.
Install the MQSYSDM.meta Datamodel this allows one to specify the SQL Server Instance to use.
Define a Connection to the isolated SQL Server Instance created for that Client
The Initial Catalog can be left blank
Site Setup Group or User Permissions – METHOD 3
In Site Setup it is possible to control who has access to Materialised Queries
It is also possible to select the Tables within the Materialised Query Database that a user has access to. (In this example you would select Company A or B and then specifiy the Tables)
