March 18, 2009

Set a database to read only mode using SQL Server 2005/2008

In this post, we will see how to set your SQL Server 2005/2008 database in a read only mode. Since there are two seperate ways of doing it for SQL Server 2005 as well as SQL Server 2008, we will create seperate queries for them.

SQL Server 2005

In order to set your SQL Server 2005 database in a read only mode, first make sure there are no users connected to the database. Once you are sure of that, use the sp_dboption stored procedure to modify database settings

Use the following query. In the query, we make the database read only after first checking if there are no users connected to the db. The Variable @CntConn = 0 means there are no users connected.

DECLARE @CntConn smallint

SELECT @CntConn = count(spid) FROM sys.sysprocesses

WHERE db_name(dbid) = 'Northwind'


if @CntConn < 1

EXEC sp_dboption "Northwind", "read only", "TRUE";

PRINT @CntConn

If someone now attempts to change any data in the database, he is encountered with the following error message

To set the database back to write only mode, use this query:

EXEC sp_dboption "Northwind", "read only", "FALSE";

Note: Now if there are a few users connected and you want to disconnect them forcefully, then check this post of mine Obtain Exclusive Access to Database While Performing Maintenance Tasks in SQL Server

SQL Server 2008

sp_dboption does work in SQL Server 2008, however it should not be used. According to BOL,
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE instead.

So to make a database read only in SQL Server 2008, use this query

USE master;





About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

Philippe said...


How could we put an SQL server 2008 in READ_WRITE mode?

Is it with this code :

USE master;


Thanks for this post!