CODEWITHSUNDEEP
sql-server
mwolfe02
mwolfe02

Reputation: 24207

Make data in database read-only but allow schema changes

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:

Any other ideas would be greatly appreciated.

Upvotes: 1

Views: 1102

Answers (2)

Piotr Rodak
Piotr Rodak

Reputation: 1941

I agree with Michael. You probably have release scripts that you run on production database. Run the DDL part of the scripts against snapshots and change them back to read only mode.

Upvotes: 0

Michael Wheeler
Michael Wheeler

Reputation: 2497

When new fields get added to the production database and the application changes (which is most likely done during a maintenance window), why not have a script run that updates the snapshots.

You can then make sure that the script changes the read only status to make the update and can log all activity. This will allow you to test your script during development using the development SQL server.

Unless I'm missing something.

Upvotes: 1

Related Questions