Post reply

Moving 2000 Sql DB w/Logins and permissions to 2005 Sql

  • September 23, 2008 at 10:33 am

    #382786

    We have no problems copying databases from Sql 2000 to Sql 2005. But when it comes to users, logins and permissions, we usually have to delete and re-add. I think there has to be a better way. I thot I could script them on the 2000 then run the script on 2005, nope, doesn't work. We've been moving some testing databases from an envireonment on Sql 2000 to the new testing envireonment on Sql 2005 and it is cumbersome to have to delete and re-add users and logins everytime.

    Someone has to know a shortcut,, hopefully..

    Thanks in advance.

  • September 23, 2008 at 10:53 am

    #875897

    you should be able to script you logins out of the 2000 server and when you create them in the 2005 server, they need to be synched back up with the users already existing in the database you copied. This can be accomplished using the sp_change_users_logins command.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • September 23, 2008 at 10:56 am

    #875901

    In the half dozen migrations we've done, we've used the good old sp_help_revlogins method from MS:

    http://support.microsoft.com/kb/246133/en-us. It outputs a script that can be executed in the SQL 2005 instance and create Windows and SQL Server logins.

    We create the logins before restoring the databases, then clean up any orphaned users we might have missed.

    Greg

  • September 23, 2008 at 3:44 pm

    #876051

    Thanks,

    That script worked, although it does all the logins for the whole server. The source and destination servers in our case are in the same domain so the sid's should be the same. I took a few of the userid's and cut out the pertinent scripting and ran them, Seems to work..

    I Already deleted and re-added the users for the DB I needed to move, so will have to try this new solution in the future,

    Thanks .. ask and you shall receive. 😀

  • September 24, 2008 at 9:39 am

    #876468

    +1 for sp_help_revlogin as well

    Note it [the user/sys table] changes a bit in SQL Server 2005

    Good thing that the script maintains the password, and the SID

    so you shouldn't have Orphan User issue on the new server either (if you copy over DB's, restore, all users should be linked and working out of box)

    No need to auto_fix orphan users

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

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

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