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;


GO


ALTER DATABASE Northwind


SET READ_ONLY;


GO



Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Set a database to read only mode using SQL Server 2005/2008"
  1. Philippe said...
    November 17, 2010 at 7:13 AM

    Hi,

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

    Is it with this code :

    USE master;
    GO

    ALTER DATABASE Northwind
    SET READ_WRITE;
    GO


    Thanks for this post!

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions