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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s