Can you temporarily turn off database changes in Microsoft SQL Server?

Solution 1:

You might have an XY Problem. There are ways to do what you're attempting that don't require the tables being operated on to be unavailable for mutation for the duration of the operation. Here's how I'd swing it (high level):

1. Add a new column with the desired definition (data type, collation, etc).
2. Use 'after insert' triggers to make sure that data mutations coming from your application also mutate the new column.
3. Backfill the existing data in the table.
4. Drop the old column, rename the new column to have the old column's name.

Solution 2:

Without further knowledge of your database schema, permissions, etc. and some idea of your strategy with respect to your application it is exceedingly difficult to be prescriptive. At a minimum, does the application even allow you to operate in a read-only mode with the table updates blocked?

At a very basic level, "Yes" you can do that... DENY INSERT, UPDATE, DELETE ON <table> TO <user> however it is impossible to say how your application will react. In my experience most applications will splatter errors everywhere and cry bloody murder if you do this, possibly even corrupting the data (unchecked errors, poor use of transactions, etc.). Rarely (possibly never) have I seen an application that gracefully switched to a read-only mode when database access was not as designed/expected.

So TEST! Test, test, test, in a controlled non-production environment until you have a documented process supporting your change.

If your requirements do not allow for an extended outage (or no outage at all) there are more sophisticated ways to handle this. One thought would be to add an after insert / after update trigger to automatically convert new /updated records in a new field while you run maintenance on smaller batches. Once all the data is converted, switch the application to the new field and drop the old one.