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.
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:
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
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|