Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    PHILIPPINES
    Posts
    9

    Talking Unanswered: User Login Migration

    Hello,

    Is it possible to migrate the user login from one SQL Server to another SQL Server if yes how? can you give a sample sp script

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    go to generate sql script on a database and choose in options to have it generate sql server logins - youll have to manually add in the passwords before using it though, as it obviously wont script those...

  3. #3
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You can use the SP in this link to get all the passwords out, this works very nicely.

    http://support.microsoft.com/default...EN-US;Q246133&

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  4. #4
    Join Date
    Sep 2003
    Location
    Columbia, MD USA
    Posts
    95
    I agree... I use that stored procedure all the time (sp_help_revlogin)

    it will transfer the logins, thier passwords and SUIDs, only thing it doesn't do is pull the current default database.

    I usually take the output from the stored procedure and throw it into notepad and do a replace that adds in the @defdb parameter to the sp_addlogin commands (with the apps I deal with it's always tempdb for the regular users so that makes it easy). Depending on the apps you're supporting it may need to be different
    drew
    ------------------
    http://wjtechnologies.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: User Login Migration

    and don't forget to fix the logins...

    DECLARE @SQL varchar(100)

    DECLARE curSQL CURSOR FOR
    select "exec sp_change_users_login 'AUTO_FIX','" + name + "'"
    from sysusers
    where issqluser = 1 and name NOT IN ('dbo', 'guest')

    OPEN curSQL

    FETCH curSQL into @SQL

    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC (@SQL)
    SELECT @SQL
    FETCH curSQL into @SQL
    END

    CLOSE curSQL
    DEALLOCATE curSQL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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