Read Only Access

[mpadgett]

The company I work for is moving off of its current business software to a new one. The current application runs on SQL 2000. We want to leave the server and the application active just for referencing history data. What would be an easy and suitable way of rendering the database read-only. At the database level or user level? Users log into the application with Database Server Authentication.

 

[Jamfool]

Database level would be a better choice, to make sure no one makes any changes.

ALTER DATABASE [myDB] SET  READ_ONLY WITH NO_WAIT

 

[gmmastros]

You can set the database to be read only, but that may not work. If you intend to run the old application to reference data in the database, then you may run in to problems with this. You see, the old application may write data to a table to log logins, or other changes. If you set the database to read only, you may not be able to log in with the old application.

To set a database to read-only....

Alter Database [!]YourDatabaseName[/!] Set READ_ONLY

To change it back...

Alter Database [!]YourDatabaseName[/!] Set READ_WRITE

I would suggest that you set the database to read only and then attempt to log in to it from the old application. If it works, then great, problem solved. If not, then you'll need to think of something else.

Restricting access to your users at the user level may be a bit problematic because it may be easy to "miss" something. I think you should do this (if necessary) but I also think you shouldn't rely on it either. If you decide to take this approach, then I would encourage you to backup the database NOW, and then restore it periodically. Then, let everyone know that they are not supposed to change anytyhing, and if they do, the changes will be lost.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

You'll need to make sure that the application supports the database running in read only mode.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog