Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    14

    Unanswered: Database Refresh -- Group ID Mismatch

    Hi,

    Doing a Database Refresh activity.

    Taken backup of PROD server wanted it to restored on UAT server.

    Before restoring it on UAT server taken backup of sysusers and sysalternates to retain the user info on the UAT server.

    Loaded UAT and done bcp in of sysusers and sysalternates.

    But now enduser is complaining about permission issue.

    Checked and found that there is Group ID mismatch.

    Gid from PROD and UAT have been different.


    How do I deal these situation. Has anybody faced such a scenario.

    Please suggest.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Generate a script with the group memberships, and all the permissions assigned to these groups. Then drop the groups, and re-add them in the same order they appear in the production system. Confirm the groups have the same GIDs. This should only have to be done once, as the bcp out of the sysusers table should cover any future refreshes.

  3. #3
    Join Date
    Jan 2009
    Posts
    14
    Thanks Mcrowley .... But I find generating the script for group permission is tedious (if tools are not used)

    How about updating the gid directly in the sysusers .... is it ok ... has anybody tried this out ...

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Updating the systables is possible, but be careful. Only do this kind of stuff if you're sure what you're doing.
    My experience is limited to Adaptive Server Enterprise, and in ASE groups are special entries in sysusers.
    What is the exact problem? Are users not part of the group with permissions? Or does the group not have the proper permissions in the database?
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Mar 2007
    Posts
    72
    use <dbname>
    go
    sp_configure 'allow updates', 1
    go
    update sysusers set a.suid = b.suid from sysusers a, master..syslogins b
    where a.name = b.name
    go
    sp_configure 'allow updates', 0
    go

    the above should reset the suid in the sysusers table in the database

    suda

  6. #6
    Join Date
    Feb 2009
    Location
    Prague
    Posts
    17
    From my practice, I would suggest to put "begin tran" before the update statement :-) And commit it only after checking that sysusers contains what is expected...

    For the first time, do it manually, record the actions in the script for particular database and next time refreshing UAT you can run this "fix" from script.
    Last edited by jannovak; 06-07-10 at 07:03.

Tags for this Thread

Posting Permissions

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