SQL 2005

Making a SQL Server Database Read-Only

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.