We are working on a number of custom reports and finding the similar queries are making up these reports.
As such, he would like to create these are views in the database. Currently there are no views that we can see in the LT database so it seems feasible but before doing so he wanted to check with us if this was okay.
Having conferred on this, we must advise against creating views in the LiveTime database. This is not something we will be able to support.
That said, there are ways to use the data in the LiveTime database in other databases, and from there create the views.
For example, in Microsoft Access, using an ODBC connection to the LiveTime database on SQL Server, it is possible to then have access to all the tables in the SQL Server database inside Access. Views, or queries as they are referred to in Access, can be created accordingly. These will be saved in the Access database, rather than in the actual LiveTime database.
In SQL Server itself, there a number of ways of exporting or transferring the data, such as snapshots, replication or subscriptions, etc. In other words, there are ways to get a copy of the data in the tables into another database. From there these Views could be added, provided they are not being added to the actual LiveTime database. For this to work any replication or transfer would need to be one way only; from the actual LiveTime database to the copy.
It is important that in all these suggestions, the user accessing the LiveTime database should utilise only read permissions. A user with db_datareader and db_denydatawriter database level roles is sufficient to ensure only reads are being done to the database.