Users and logins transfer !! RRS feed

  • Question

  • Hi Everybody,
    Here is the scenario: I have s2k production and development servers, we are moving to  s2k5 environment. Since the backup of production servers have latest data, so i restored the databases from the backups of production server in 2000 to the new development servers in s2k5 environment. Along with the restore of databases from production, the users also get moved. However, these are not the users that we want in new ss2k5 development server. We the users and logins from s2k development servers.
    My questions:
    1) If i execute sp_help_revlogins in s2k development box and run the output in new development box, will it solve my problem?
    2)I know it will create logins, will it create the database users too? If not, what steps should i have to take?

    Wednesday, April 15, 2009 3:31 PM

All replies

  •    I am assuming you refer to sp_help_revlogin defined at http://support.microsoft.com/kb/246133/, correct? If this is the case, then this SP should definetly help you to move the logins from your SQL Server 2000 instance to SQL Server2005.

      For the DB users, I would assume that you are migrating the DBs using BACKUP and restoring on the new server, this should cover the users, and since you are migrating the logins, there should be no orphaned users.

      Please let us know if you experience any problems during the migration.

      BTW. Since you are in the process of migrating from SQL Server 2000, I would like to suggest migrating directly to SQL Server 2008. The migration between SQL Server 2000 to 2008 should not be much different than the one from 2000 -> 2005.

     - Raul Garcia
       SQL Server Engine

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 15, 2009 10:11 PM
  • Thanks Raul,

    I know, there should not be any problem. But in the environment I am working on, I suspect that there will be. Thats why i want to take precaution.

    As i have already mentioned, I have to migrate development databases in 2000 box to 2005. While restoring those databases to new 2005 development box, we used the backups of production databases...hence now the new databases have the DB users from production which  are not the development users. If i use sp_help_revlogins in 2000 development box and run its output in new 2005 development box, it will surely transfer the sql server logins. But, my doubt is: will it also transfer/create the database users from 2000 develooment databases to new 2005 development databases?

    I just want to be confirmed, before i moved ahead.

    Thursday, April 16, 2009 1:26 PM