Read_Only Database

  • Comments posted to this topic are about the item Read_Only Database

    John Smith

  • I ran this statement against a SQL Server 2000 database and got this message:

    Msg 5058, Level 16, State 5, Line 1

    Option 'READ_ONLY' cannot be set in database 'MASTER'.

    I think the correct answer is E. Anyone else agree with me on this?

  • I agree, but I only ran this against 2005 instance (no access to 2000).

    makes more sens to not have it read only in the first place.

    On 2008 it's definitly not possible to make it read only.

  • This is what I got when I ran it in 2005...

    Msg 5058, Level 16, State 5, Line 1

    Option 'READ_ONLY' cannot be set in database 'master'.

  • The original question was asked about 2000, but I tried 2000,2005,2008 and they all gave me the same error. Now the article that was referenced did mention that it could be done on a 2000 master database, but I wonder if one of the service packs changed this functionality this... Still I think the question is invalid.

    Cheers,

    Brian

  • Yes, I tried on Microsoft SQL Server 2000 - 8.00.2039 (Standard Edition) this option is disable on Master database, could be Enterprice edition might be having... question should specify the version and edtion of 2000... else the answer E is right...I want my points.:-D

    Thanx

    Vinay

    Thanx.
    Vinay

    http://rdbmsexperts.com/Blogs/
    http://vinay-thakur.spaces.live.com/
    http://twitter.com/ThakurVinay

  • It's a bit hard to believe that 67% of the people are wrong about this ;).

  • Even though BOL says it can be done, I tried it on Microsoft SQL Server 2000 - 8.00.2050 Enterprise Edition and it fails there also.

  • I suspect that the reason the error message was because the master database was in use, not because it is invalid to set read only on master. Since all the background processes use master how can there be no users to set read only?

  • Maybe using DAC to connect or having the whole server in single user... but then what's the point of setting master to read_only??

  • When I attempt to put a user database in Read_Only when it is in use, the process hangs waiting for the database to be freed up. I would seem consistent that it would do the same thing for Master if that option were valid.

  • Sorry, the question has been corrected after testing on a couple instances. Points awarded back.

    It appears, even if you are not connected to master, you cannot set this option.

  • I tried starting a 2000 SP3 Standard Edition with single user mode with no luck. DAC didn't come along until SQL 2005 so that wouldn't work. Is there anyway to actually make this available!?!

  • http://msdn.microsoft.com/en-us/library/aa933268(SQL.80).aspx

    BOL states:

    When true, users can only read data in the database. Users cannot modify the data or database objects, however, the database itself can be deleted using the DROP DATABASE statement. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read only option is being set.

    This indicated to me that the master database has the option available...:hehe: - o well..

  • barb.wendling (4/12/2009)


    http://msdn.microsoft.com/en-us/library/aa933268(SQL.80).aspx

    BOL states:

    When true, users can only read data in the database. Users cannot modify the data or database objects, however, the database itself can be deleted using the DROP DATABASE statement. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read only option is being set.

    This indicated to me that the master database has the option available...:hehe: - o well..

    We all read that in bol, but noone's been able to set to read_only (even using sa).

    That's why the correct answer's been updated in this one!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply