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;

GO

ALTER DATABASE Northwind

SET READ_ONLY;

GO

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook