This is a clarification of an earlier question I asked. It is sufficiently different from that question that I thought it could stand on its own.
I create snapshots of a production database semi-annually. Users are then able to view historical data by switching the back-end database from the front-end GUI. Because it is historical data, it must remain read-only.
However, sometimes new fields get added to the tables in the current database. This can cause problems if the front-end expects these fields to be present. Our solution is to add the fields on the fly, and set them to null or an appropriate default if the field is required. What this means is that the database cannot be made read-only because that would prevent changes to the table structure. To address this we give all users db_datareader, db_denydatawriter, and db_ddladmin roles.
All of this works really well and we have had no problems. However, the data can still be changed by dbo (which is me). My concern is that someday I will accidentally make a change to this historical data (probably without realizing it) and thus compromise its integrity.
Attempted Solutions:
I tried assigning the role of db_denydatawriter to dbo but that failed (see the aforementioned question).
Another suggestion from that question was to turn off and on the read-only status of the snapshot when making schema changes. That works, except that it causes roughly a two-minute delay per snapshot schema change. We don't generally make a lot of schema changes, but I'd like to avoid the situation if possible.
Any other ideas would be greatly appreciated.
