Post reply

Help needed to alter database in restoring state

  • March 2, 2009 at 9:37 pm

    #131481

    Hi all...

    I have my database in ms sql server 2008.I want to implement database mirroring..

    I have restore database on secondary server with norecovery.After the database was restored, i executed the the procedure sp_change_user_login but that was not executed sucessfully.

    Plz suggest how can I change the user login(under the security) after the database is restore with norecovery option.

    thx in advance....

  • March 3, 2009 at 5:02 am

    #952335

    You can't execute anything against the mirror database unless it's brought online and if it's brought online you won't be able to implement database mirroring.

    Drop the logins on the mirror server, script them from the principal with the SIDs and use that script to create the logins on the mirror. Then the users won't be orphaned and you won't need to run change_user_login.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 3, 2009 at 5:12 am

    #952339

    You need to transfer logins prior to database restoration so tht restoration doesn't result in orphan users.

    For more information on Transfer Logins Task:

    · Transfer Logins Task http://technet.microsoft.com/en-us/library/ms137870(SQL.90).aspx

    MJ

Viewing 3 posts - 1 through 2 (of 2 total)

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