Post reply

Transfer logins and passwords from SQL Server 2000 to SQL Server 2008

  • January 6, 2011 at 4:52 am

    #1269999

    the micosoft methods in technet work absolutley fine between versions. have used for sql 2000 to 2008 with no problems and now use this as part of DR to keep logins and sids in step on log shipped servers. ( not using ms log shipping )

    As always you should test everything ( seevral times ) before you make the final upgrade. The first thing I did after reading the technet methods was to try it out to see if it worked on a test server ( it did )

    I'd suggest migrating the logins will be the least of your problems migrating from 2000 to 2008 < grin >

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 11, 2011 at 9:59 am

    #1285792

    Charles:

    I am trying to leapfrog SQL 2005 and go directly from 2000 to 2008. I tried sp_script_login.txt, but it uses LOGINPROPERTY, which is not available in 2000.

    Does anyone know if the SQL Server installers do horrible upgrades like Windows installers do?

  • February 11, 2011 at 10:28 am

    #1285803

    There should also be a sp_script_login_2005.txt which works for 2005 and above.

    I misread your question. I've reposted what I currently use for SQL Server 2000 servers. It runs daily with out issue on the few that we still have.

  • March 17, 2011 at 10:30 am

    #1300059

    Hi Charles,

    This looks like a nice script. Is there a reason it does not add the server roles for Windows logins?

    2nd Edit: Now that I look at it closer it looks like it should. But for some reason the EXEC sp_addsrvrolemember statements are not being generated for a particular Windows login. Any ideas? I'm running sp_script_login on a SQL 2000 database.

    Sorry - 3rd Edit: I must have been using an older copy or something. The one I was using did not have the following in the -- NT authenticated account/group section.

    select @OutputString =char(9)+'EXEC sp_addsrvrolemember '+char(39)+@name+char(39)+','+char(39)+p.Permission+char(39)

    from sysxlogins l

    join @SvrPerms p on p.xstatus&l.xstatus=p.xstatus

    where l.name=@name

    Still it only seems to add the first role for the login.

    Kevin

  • July 17, 2012 at 2:01 am

    #1513445

    yep, this one worked well for me also. i did upgrading projects of our legacy systems from 200 to 2008 and this scripts were a big help. thanks much 🙂

    Cheers! 🙂
    [/url]

Viewing 5 posts - 16 through 19 (of 19 total)

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