How to set database read only/read write?

In SQL Server 2005 using the SQL below one can make the database read-only/read-write.

— set database to read only
ALTER DATABASE AdventureWorks SET READ_ONLY
— to view/check database is read only
EXEC sp_dboption ‘AdventureWorks’, ‘read only’

OptionName CurrentSetting
———————————– ————–
read only ON

— set database read write
ALTER DATABASE AdventureWorks SET READ_WRITE
— to view/check database status
EXEC sp_dboption ‘AdventureWorks’, ‘read only’

OptionName CurrentSetting
———————————– ————–
read only OFF

One can specify termination mode termination mode when changing database read mode.

In SQLServer 2000, one can change database to read-only using sp_dboption.
EXEC sp_dboption ‘AdventureWorks’, ‘read only’, ‘TRUE’

To view/find database is in read only/read write by querying sys.databases, if the value of is_read_only is 0 the database is in read/write, if returns 1 then database is in read only mode.
SELECT name, is_read_only from sys.databases WHERE name = ‘model’
name is_read_only
————- ————
model 0

Leave a Reply