MSSQL READ_ONLY

Databases

SQL Server

MSSQL Configuration

Check

EXEC sp_dboption 'myDB', 'read only'

Change

To set a database to be READ ONLY...

ALTER DATABASE [myDB] SET READ_ONLY WITH NO_WAIT;

To set a database back to READ WRITE...

ALTER DATABASE [myDB] SET READ_WRITE WITH NO_WAIT;

NOTE: If any users are using the database you will get an error when attempting these changes. If you are unable to find a time where there are no users then consider forcing the users to disconnect by putting the database into single-user mode.

Make sure you understand the implications of doing this. i.e. you will be able to convert the database to READ ONLY/READ WRITE but what problems will you have caused for the connected users/applications?

ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

To revert to multi-user use...

ALTER DATABASE [myDB] SET MULTI_USER;

Bibliography

https://aodba.com/set-sql-server-database-read-read-write-mode-2/https://www.dbrnd.com/2016/03/sql-server-script-to-make-database-read-only-and-read-write/https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode?view=sql-server-ver15

Page updated

Google Sites

Report abuse