CODEWITHSUNDEEP
sql-serverdatabaset-sqlreadonly
Giuseppe
Giuseppe

Reputation: 655

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

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?

Upvotes: 39

Views: 74154

Answers (6)

farhad hossain
farhad hossain

Reputation: 1

If you would like to check all DB statuses in your server, use this:

SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc  
FROM sys.databases;

You can quickly determine your next steps.

Upvotes: 0

Mehrdad Alemi
Mehrdad Alemi

Reputation: 129

If DB is part of your Always On and the secondary node is designed in Read_Only then "sys.databases --> Is_Read_Only" column wont show the correct result ! its a bug that Microsoft needs to address it during the next versions.

Upvotes: 0

Masood Hashim
Masood Hashim

Reputation: 561

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');

Upvotes: 46

Rafal Zajac
Rafal Zajac

Reputation: 1653

I was trying to use the p.campbell's answer to check if my Azure SQL DB is the primary one or the read only replica - it didn't work. Both the primary DB and the replica returned had 0 on the is_read_only field.

Here's what worked for me:

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

the above select statement returns string 'READ_ONLY' or 'READ_WRITE'.

Upvotes: 9

JohnFx
JohnFx

Reputation: 34909

Here is a command to display or set this property. 

EXEC sp_dboption "AdventureWorks", "read only"

Sample output 

OptionName CurrentSetting    
read only OFF

Upvotes: 6

p.campbell
p.campbell

Reputation: 100557

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.

Upvotes: 45

Related Questions