locked
Making data change in read_only database without letting other users update data RRS feed

  • Question

  • I want to make data changes in read_only database , that's why i must set database read_write.

    while database is at read_write mode, i want to be sure that no one makes change in database.

    for this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database

    single_user ,is it possible get DML script from another user.

    Is the code below enough for this operation. Or is there another way?

    Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.

    The code;

    use master


    alter database xxx set read_write
    with rollback immediate

    alter database xxx set single_user
    with rollback immediate


    use xxx



    update  tablexxx set columnxxx=yyy


    use master

    alter database xxx set read_only
    with rollback immediate

    alter database xxx set multi_user
    with rollback immediate

    Thursday, August 6, 2015 1:30 PM

Answers

  • Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.

    When I test, I seem to be able to set a READ_ONLY database to SINGLE_USER.

    Be sure that you have the database in question as the current database when you set it single user - then no one else can creep in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 6, 2015 9:57 PM

All replies

  • Give all users the datareader permission on your database.


    Please click "Mark As Answer" if my post helped.

    • Proposed as answer by ediguru Thursday, August 6, 2015 1:50 PM
    Thursday, August 6, 2015 1:40 PM
  • If there is only one user database on this Server then you could: -

    • Kill all user processes
    • Disable the option to allow remote connections
    • Log onto the Server locally and then into SQL Server
    • Do your changes
    • Set the database back to read only and then ensable the option to allow remote connections;


    Please click "Mark As Answer" if my post helped. Tony C.

    Thursday, August 6, 2015 1:49 PM
  • Why not use LOCK and UNLOCK on the specific record or field and do not allow access to the entire Table in a Database.

    EDIGURU

    Thursday, August 6, 2015 1:53 PM
  • Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.

    When I test, I seem to be able to set a READ_ONLY database to SINGLE_USER.

    Be sure that you have the database in question as the current database when you set it single user - then no one else can creep in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 6, 2015 9:57 PM
  • thanks, i think i had a fault while  executing script
    Monday, August 10, 2015 11:00 AM