SQL 2005+

Making a SQL Server Database Read-Only

by Richard Carr, published at http://www.blackwasp.co.uk/SQLReadOnly.aspx

There are many situations where it is important that users are unable to modify the contents of a database. For example, if data is to be migrated to another server or for reporting purposes. This tip explains how to set a database to a read-only mode.

ALTER DATABASE Command

The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes permitting the changing of database configuration options.

Setting a Database to Read-Only

When you need to ensure that the data is a database is not modified by any users or automated processes, it is useful to set the database into a read-only mode. Once read-only, the data can be read normally but any attempts to create, updated or delete table rows is disallowed. This makes the read-only mode ideal when preparing for data migration, performing data integrity checking or when the data is only required for historical reporting purposes.

To make a database read-only, the following command is used:

Setting a Database to Read-Write

If the read-only requirements for the database are temporary, you will need to reset the configuration option following any procedures undertaken. This is achieved with a small modification to the ALTER DATABASE statement to indicate that the database should return to a writeable mode.