SQL Server: Make Your Database Read-Only or Read-Write

Sometimes you may need to make a SQL Server database read-only for administrative or maintenance purposes.

Before setting a database to read-only, make sure that no active operations or users require write access to the database.

Using ALTER DATABASE

To set a database to read-only:

ALTER DATABASE TestDB1 SET READ_ONLY;

Once the database is read-only, users can query the existing data, but write operations such as INSERT, UPDATE, and DELETE are not allowed.

If the database was set to read-only temporarily and you want to allow write operations again, use:

ALTER DATABASE TestDB1 SET READ_WRITE;Using SP_DBOPTION

Older versions of SQL Server provided the sp_dboption system stored procedure for changing database options.

For example:

EXEC sp_dboption 'TestDB1', 'READ ONLY', 'TRUE';

However, sp_dboption is deprecated. For current SQL Server versions, it is recommended to use ALTER DATABASE instead.

Denying INSERT and UPDATE Permissions

 Another approach is to prevent specific users or roles from modifying particular tables rather than making the entire database read-only.

For example:

DENY INSERT, UPDATE ON dbo.TableName TO UserName;

The DENY permission takes precedence over a corresponding GRANT, so this approach can be useful when you want users to have access to the database but prevent modifications to specific tables.

Choose the approach based on your requirement:

• Use ALTER DATABASE ... SET READ_ONLY when the entire database should be read-only.

• Use DENY when only specific users or tables need to be protected from modifications.