Post reply

Orphaned local groups after upgrading from sql 2000 sp 4 to sql 2005 sp2

  • November 12, 2007 at 7:00 am

    #179265

    Dear all,

    Last Saturday did a site by site upgrade from a certain sql 2000 sp4 machine. I took following steps:

    • Installed w3k enterprise edition including the needed sericepacks

    • Installed sql 2005 including sp2

    • transferred all local groups from the old server to the new server thru the 'adduser' tool. (changed the names to the names of the new machine)

    • Stopped sql related services on the old machine (sql 2000 sp4)

    • This step: http://support.microsoft.com/kb/918992 -> sp_help_revlogin first on the old server then on the new server

    • Copied all ‘ldf’ and ‘mdf’ files from the old server to the new server

    • Attached all databases (formerly running on the old server)

    • All domain related sql logins are working properly

    • Except the local groups, it look likes I forgot something

    This is the problem looks like the group members or the group are orphaned users, normally I ran the sp_sidmap procedure and the problem was solved. This is not allowed under sql 2005.

    Any one has a guess why things are not working properly ?

    Second question: ‘Is it possible to script out database users on a sql 2000 machine and load the results on a new sql 2005 machine ?

    All help would be gratefully appreciated.

    John

  • November 12, 2007 at 8:56 am

    #750287

    The results of sp_helprevlogin applied to the new server should work if the domain is the same, if there were problems then you will get error messages indicating which logins have failed. I have used it myself between 2000 and 2005.

    Use EXEC sp_change_users_login 'Report' to see which logins are now the issue.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • November 12, 2007 at 9:26 am

    #750300

    You can not move local Windows users and Local Windows groups. Local users and groups have SIDs that have part of the number come from the server. That is why Microsoft advises to create Global groups and then add them to local groups if required.

    You will need to re-create and re-add the local groups with tis permissions.

    As for scripting the users, I do that in 2 ways. First, with the help of sp_help_revlogin as mentioned above. For some reason lately 2 of the logings moved this way lost their password's upper case.

    The second way is to script the logins with the new passwords, I do it for the cases when the applications can take care of password expiration and force the users to change the password on the next logon

    Regards,Yelena Varsha

  • November 14, 2007 at 10:43 pm

    #751375

    Yelena Varshal (11/12/2007)

    You can not move local Windows users and Local Windows groups. Local users and groups have SIDs that have part of the number come from the server. That is why Microsoft advises to create Global groups and then add them to local groups if required.

    This causes the same issue. If login rights within SQL Server are being granted to local groups on the server, you will have this issue whenever you change servers. The way around this is universal, domain global, and domain local groups.

    With that said, how did the local groups map into the individual databases? Did they map in as COMPUTERNAME\LocalGroup or just LocalGroup? If the latter, you can drop and re-grant the logins in SQL Server and then in each individual database run sp_change_users_login for each user affected.

    K. Brian Kelley
    @kbriankelley

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

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