How to detect a SQL Server database's read-only status using T-SQL?

Question

I need to know how to interrogate a Microsoft SQL Server, to see if a given database has been set to Read-Only or not.

Is that possible, using T-SQL?

Answer 1

The information is stored in sys.databases.

SELECT name, is_read_only  FROM sys.databases  WHERE name = 'MyDBNAme' GO  --returns 1 in is_read_only when database is set to read-only mode. 

Answer 2

Querying sys.databases for checking a DB's Read-Only property will only give the right information if the database has been explicitly set to Read-Only mode.

For databases that are in the passive servers (e.g. in AlwaysOn technology Secondary Servers), even though the databases cannot be written into, their Read-Only mode in sys.databases would still be set as False(0).

Hence, it is advisable to check the Read-Only mode of databases using the statement:

SELECT DATABASEPROPERTYEX('MyDBNAme', 'Updateability');