Post reply

Move Logins with Password from SQL 2000 to SQL 2005

  • June 3, 2009 at 4:30 pm

    #132405

    Hi

    Currently we have a SQL 2000 server and on which there are 1000+ logins on the server and we are planning to move to SQL 2005 and stucked up when i have to move the logins with thier passwords to the new server. Is there anyway we can do that?

    Please reply me with suggestions and solutions or for further details

    Thanks

    JB

  • June 3, 2009 at 4:36 pm

    #1004340

    Given below link explains you how to migrate logins and password.

    http://support.microsoft.com/kb/246133/

  • June 4, 2009 at 2:54 am

    #1004476

    I Have also the same problem of this.

    Thanks

  • June 4, 2009 at 11:52 am

    #1004819

    thanks a lot... let me try that

  • June 4, 2009 at 10:21 pm

    #1005011

    I have moved the login from 2000 to 2005 yet the new logins has no password and no property can be edit.How Can I set the password of transferred logins

    Thanks

  • June 5, 2009 at 8:21 am

    #1005306

    You can run this query on the SQL 2000 server to create commands to set the passwords on the SQL 2005 server.

    SELECT 'ALTER LOGIN [' + l.[name] + '] WITH PASSWORD = ', x.password, ' HASHED'

    FROM master.dbo.sysxlogins x

    INNER JOIN master.dbo.syslogins l ON l.sid = x.sid

    WHERE hasaccess=1 AND isntname=0

    This query will create the complete SQL 2005 CREATE LOGIN commands to copy logins when run on SQL 2000 or SQL 2005.

    SELECT 'CREATE LOGIN [' + l.[name] + '] WITH PASSWORD = ', x.password, ' HASHED , SID = ', l.sid, ', CHECK_POLICY = OFF'

    CASE WHEN dbname 'master' THEN ', DEFAULT_DATABASE = [' + dbname + ']' ELSE '' END

    + CASE WHEN l.[language] IS NOT NULL THEN ', DEFAULT_LANGUAGE = ' + l.[language] ELSE '' END

    FROM master.dbo.sysxlogins x

    INNER join master.dbo.syslogins l ON l.sid = x.sid

    WHERE hasaccess=1 AND isntname=0

    The results grid of both queries can be cut and pasted to a SSMS window to produce a working script, without worrying about converting the varbinary values to text. If you run this with results as text it will still work, but there is a huge amount of white space after the password and sid binary values.

    You may also want to add a GO between every line to make it continue after errors. (A common cause of errors is if the DEFAULT_DATABASE value from SQL 2000 doesn't exist on the SQL 2005 server. You may want to delete that clause.)

    ... END + '

    ' + 'GO'

    FROM ...

    If you have a linked server from SQL 2005 to the SQL 2000 server, you can use the fn_varbintohexstr function to do a neater job.

    SELECT 'CREATE LOGIN [' + l.[name] + '] WITH PASSWORD = '

    + CASE WHEN x.password = 0x0 THEN '''''' ELSE sys.fn_varbintohexstr(x.password) + ' HASHED' END

    + ', SID = ' + sys.fn_varbintohexstr(l.sid)

    + ', CHECK_POLICY = OFF'

    + CASE WHEN dbname 'master' THEN ', DEFAULT_DATABASE = [' + dbname + ']' ELSE '' END

    + CASE WHEN l.[language] IS NOT NULL THEN ', DEFAULT_LANGUAGE = ' + l.[language] ELSE '' END

    FROM [SQL2000].[master].dbo.sysxlogins x

    INNER join [SQL2000].[master].dbo.syslogins l ON l.sid = x.sid

    WHERE hasaccess=1 AND isntname=0

  • June 8, 2009 at 2:49 am

    #1005960

    There are scripts in FineBuild that can migrate SQL logins complete with their original passwords. You can get FineBuild from the link below or direct from CodePlex. Look in the Reference manual for the section about upgrading SQL Server.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 7 posts - 1 through 6 (of 6 total)

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