Post reply

Need to regenerate security after restore

  • January 31, 2013 at 4:02 am

    #394073

    I am loading a production database into a QA environment each night and wanted to know the most efficient way to 'resynch' the users and their logins?

    Can I use 'alter user with login' in 2005? Currently I am dropping the users and recreating them but it is giving me a ton of errors as a lot of users own objects in the database.

    Any help would be appreciated.

    Thanks

  • January 31, 2013 at 4:06 am

    #1582866

    Drop the logins on QA. Script them from prod with their SIDs, run that script on QA. You need to do that once and only once, after that there will be no more orphaned login problems after a restore.

    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

  • January 31, 2013 at 4:14 am

    #1582869

    The issue with that is the passwords. There are about 250 users using SQL auth.

    This db is used for an internal app and there's no way the app can handle it if I specify a change password required at next login.

  • January 31, 2013 at 4:24 am

    #1582871

    then scripting out the alter login commands the one time is your best bet, and running the script after each restore. If the amount of logins constantly changes, use dynamic sql

    ---------------------------------------------------------------------

  • January 31, 2013 at 5:48 am

    #1582913

    jamie_collins (1/31/2013)

    The issue with that is the passwords. There are about 250 users using SQL auth.

    Then script the logins from QA with hashed passwords, script the logins from prod with SIDs and do a little editing so that you end up with create login statements that have the hashed password from QA and the SID from Prod. Drop all logins on QA, run that script then you'll have the old QA passwords and you'll never have orphaned login problems again.

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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