SQL Make your Database Read Write Only

SQL Make your Database Read Write Only

This post help you to make database read/write only,Before make read only be sure that no user is connected to that database.After confirm SP_DBOPTION will successfully set the status of Database.

1) By using ALTER TABLE
--SET DATABASE READ/WRITE ONLY
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.
ALTER DATABASE Database_name SET READ_WRITE

The status read-only requirements for the database are temporary, but sometimes we need it for better administrative or Command over multiple user.
ALTER DATABASE Test_Manoj set READ_ONLY

2) By using SP_DBOPTION
EXEC SP_DBOPTION "Database_name", "READ ONLY", "TRUE"
EXEC SP_DBOPTION "Test_Manoj" ,"READ ONLY","TRUE"

The other possibility is to DENY tthe INSERT and UPDATE rights on the table that you want to set Read-Only. The Deny always override the Allow right. This way, your user will be part of the db_datawriter and you will explicitly override (and deny) the insert and update rights on some table.


Follow Link-http://javadevelopersguide.blogspot.com



By Manoj

download file now