none
Log Shipping - Database Owner Change

    Question

  • Fellow SQLers

    I am trying to do some security cleanup. A database, that is the secondary for log shipping, has a database owner that needs to be changed.  I am not able to change the database owner thru the gui because the db is in standby/readonly mode.

    This is on SQL 2008 R2.

    Is there any way to do this while it is in standby/readonly mode?

    Any other options?

    MG

    Wednesday, July 11, 2018 12:58 PM

Answers

  • No, you would have to break log shipping and re-initialize it, but logged in as the user you want to be the owner when performing the restore (otherwise, it'll use the name of the person doing it as owner)

    Changing the database owner updates the SID and it's not allowed on a read-only database for obvious reasons. 

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    • Edited by Mohsin_A_Khan Wednesday, July 11, 2018 2:17 PM
    • Marked as answer by mg101 Wednesday, July 11, 2018 3:18 PM
    Wednesday, July 11, 2018 2:13 PM

All replies

  • No, you would have to break log shipping and re-initialize it, but logged in as the user you want to be the owner when performing the restore (otherwise, it'll use the name of the person doing it as owner)

    Changing the database owner updates the SID and it's not allowed on a read-only database for obvious reasons. 

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    • Edited by Mohsin_A_Khan Wednesday, July 11, 2018 2:17 PM
    • Marked as answer by mg101 Wednesday, July 11, 2018 3:18 PM
    Wednesday, July 11, 2018 2:13 PM
  • Thanks Moshine. That is what I thought but was hoping for am easier work around - none. :)

    MG

    Wednesday, July 11, 2018 3:19 PM