Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Arrow Unanswered: Login vs. Userid

    When I restore a database from the production environment to the
    test environment without restoring master, I break the link
    between master..syslogins and each userid in the databases sysusers table. (I cannot restore master as multiple instances of unrelated databases exist on the dataserver)

    Can I update the userid's SID entry in the sysusers table to match
    what is currently listed for the related login in master's syslogins table,
    or are there other columns that are used to create the link between the
    userid and login id???

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You can try update sids but my advice - recreate all logins with sids from your production server:

    sp_addlogin [ @loginame = ] 'login'
    [ , [ @passwd = ] 'password' ]
    [ , [ @defdb = ] 'database' ]
    [ , [ @deflanguage = ] 'language' ]
    [ , [ @sid = ] sid ]
    [ , [ @encryptopt = ] 'encryption_option' ]

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    Is it to be assumed that your solution is counting on the Login being
    exclusive to the userid being restored?

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Create all logins and than restore dbs.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    During creating logins it needs to set SIDs from production server...
    See my first post.

    Originally posted by rdjabarov
    snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.
    Last edited by snail; 09-05-03 at 12:55.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rocket39,

    I have a TSQL script for realligning user ids at my home. I'll post it for you this evening.

    blindman

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    I think rhigdon's link contains all the necessary scripts to do just that.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is a slight difference, in that my code realligns existing user logins for a given database to match those for the server.

    Not as extensive as the code in the link, but usefull if you can't create a particular user under the same ID as another server because the ID is being used, or if there are objects that belong to the user and you don't want to have to drop and recreate them before synchronizing IDs.

    blindman

  11. #11
    Join Date
    Sep 2003
    Posts
    522
    There is also sp_change_users_login to do sid alignments.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Those Microsoft bastards stole my code!

    Know any good lawyers?

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •