We have a 3rd party company hosting company's data at their site. We were given only a READ option to the database. We need to create and run reports off it but our report designer said that he would need to create views and temp tables as well.
I think our options are:
(1) SSIS the tables that we need and run the reports in our SQL server.
(2) Maybe get a full backup and restore this on our SQL server on a schedule.
First option is implement the Transactional Replication,if you dont want to implement this
Your report designer can create linked Server on their server and then create views and temp table also on their database server with read only right on your server,after they create all views then pass the script to your DBA to execute your database
Give db_owner rights on the tempdb for temp table and create another schema for that report designer user