Transferring Logins From SQL 2000 To 2008

During a recent migration from SQL 2000 to SQL 2008, I needed to move logins from the old server to the new server. We use mixed mode authentication, so we had both Windows and SQL logins that needed to be moved.

My first attempt was using SSIS. I created a package on the 2008 server and used the Transfer Logins task to move the logins to the new server. Piece of cake. I set it up, made sure to specify that the SIDs needed to be transferred as well, ran it and it worked fine. Then I went to test the logins and ran into a problem. All the Windows logins worked, but the SQL logins did not. Turns out, BOL says the Transfer Logins task will transfer SQL logins, but it changes their password to a random string – and doesn’t tell you what that string is. I’m sure it does this as a security precaution, but really, this makes this task pretty much worthless, in my opinion. If I need to go and manually change the passwords of all the SQL logins I just transferred, I may as well create them from scratch in the first place. Furthermore, many companies, like mine, have programs using SQL logins whose passwords have long been forgotten. Sure, I could hunt down the programs using them and try to find the configuration files that likely have the password in them, but that’s a time consuming process. And as a DBA, my area of knowledge doesn’t extend to configuring dozens of programs, each of which might store its configuration data in a different way, so I would need to pull in other resources in I wanted to go this route.

Luckily, there is another way. Microsoft has supplied some scripts to transfer logins, namely sp_hexadecimal and sp_help_revlogin. I created and ran these procedures on the 2000 server, executed the output on the 2008 server and… the logins still didn’t work. Hmm. A Google search showed lots of people were experiencing this same problem when they tried to move logins between SQL 2000 and 2008. Some claimed the two procedures mentioned before worked, but they didn’t for me.

It turns out, I just needed to read a little more carefully and think a little bit. SQL Server changed the way it hashed passwords between versions, so going straight from 2000 and 2008 won’t work. But, as they say, there is more than one way to skin a cat.* Method 2 of this Microsoft KB article details how to transfer a login from SQL 2000 to SQL 2005. And this Microsoft KB article details how to transfer a login from SQL 2005 to SQL 2008. I happened to have a SQL 2005 server hanging around, so I transferred the logins to there, then transferred them from there to the 2008 server. That worked!

If you decide to do this, there are a couple of things to watch out for. The script will set logins to have the database roles and default databases they have on their original server. Those might not exist on the intermediate server, or even the final server, so you’ll want to examine the output scripts of the procedures and remove any references to databases or roles that do not exist on the destination server. And watch out for logins that might already exist on the intermediate server. To keep things tidy and mitigate security risks, be sure to delete the logins from the intermediate server after you are done.

* At what point in history was cat-skinning so popular that more than one method was needed?

6 thoughts on “Transferring Logins From SQL 2000 To 2008

  1. Thanks for the clear real world summary. In a world of copy and paste, clear and well written information is getting to be at a premium.

    If you’ve already created the logins using the SSIS path(like me) and are swearing about the lack of passwords you can take the output from the intermediate server and change it from CREATE LOGIN to ALTER LOGIN and only include the hashed password. That just saved me some rebuilding time.

  2. I don’t know if it’s still relevant, but i found this script witch works for me. It was made up of all possibilities. Check it out!
    ——————————————————————–

    — Code For Login Migrations Between SQL Server 7 and SQL Server 2000
    USE master
    GO
    IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
    AS
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = ‘0x’
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = ‘0123456789ABCDEF’
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint – (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @txtpwd sysname
    DECLARE @tmpstr varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name ‘sa’
    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
    OPEN login_curs
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    IF (@@fetch_status = -1)
    BEGIN
    PRINT ‘No login(s) found.’
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = ‘/* sp_help_revlogin script ‘
    PRINT @tmpstr
    SET @tmpstr = ‘** Generated ‘
    + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
    PRINT @tmpstr
    PRINT ”
    PRINT ‘DECLARE @pwd sysname’
    WHILE (@@fetch_status -1)
    BEGIN
    IF (@@fetch_status -2)
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN — NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN — NT login is denied access
    SET @tmpstr = ‘EXEC master..sp_denylogin ”’ + @name + ””
    PRINT @tmpstr
    END
    ELSE BEGIN — NT login has access
    SET @tmpstr = ‘EXEC master..sp_grantlogin ”’ + @name + ””
    PRINT @tmpstr
    END
    END
    ELSE BEGIN — SQL Server authentication
    IF (@binpwd IS NOT NULL)
    BEGIN — Non-null password
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    IF (@xstatus & 2048) = 2048
    SET @tmpstr = ‘SET @pwd = CONVERT (varchar(256), ‘ + @txtpwd + ‘)’
    ELSE
    SET @tmpstr = ‘SET @pwd = CONVERT (varbinary(256), ‘ + @txtpwd + ‘)’
    PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name
    + ”’, @pwd, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘
    END
    ELSE BEGIN
    — Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name
    + ”’, NULL, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘
    END
    IF (@xstatus & 2048) = 2048
    — login upgraded from 6.5
    SET @tmpstr = @tmpstr + ”’skip_encryption_old”’
    ELSE
    SET @tmpstr = @tmpstr + ”’skip_encryption”’
    PRINT @tmpstr
    END
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    —– End Script —–

    — Code For Login Migrations From SQL Server 7/2000 to SQL Server 2005/2008

    USE master
    GO
    IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
    AS
    DECLARE @charvalue varchar(256)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = ‘0x’
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = ‘0123456789ABCDEF’
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint – (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END
    SELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin_2000_to_2005
    GO
    CREATE PROCEDURE sp_help_revlogin_2000_to_2005

    @login_name sysname = NULL,
    @include_db bit = 0,
    @include_role bit = 0

    AS
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @dfltdb varchar (256)
    DECLARE @txtpwd sysname
    DECLARE @tmpstr varchar (256)
    DECLARE @SID_varbinary varbinary(85)
    DECLARE @SID_string varchar(256)

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR STATIC FOR
    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
    FROM master.dbo.sysxlogins
    WHERE srvid IS NULL AND
    [name] ‘sa’
    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), ‘master’)
    FROM master.dbo.sysxlogins
    WHERE srvid IS NULL AND
    [name] = @login_name

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

    IF (@@fetch_status = -1)
    BEGIN
    PRINT ‘No login(s) found.’
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END

    SET @tmpstr = ‘/* sp_help_revlogin script ‘
    PRINT @tmpstr
    SET @tmpstr = ‘** Generated ‘
    + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
    PRINT @tmpstr
    PRINT ”
    PRINT ”
    PRINT ”
    PRINT ‘/***** CREATE LOGINS *****/’

    WHILE @@fetch_status = 0
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr

    IF (@xstatus & 4) = 4
    BEGIN — NT authenticated account/group
    IF (@xstatus & 1) = 1
    BEGIN — NT login is denied access
    SET @tmpstr = ” –‘EXEC master..sp_denylogin ”’ + @name + ””
    PRINT @tmpstr
    END
    ELSE
    BEGIN — NT login has access
    SET @tmpstr = ‘IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ”’ + @name + ”’)’
    PRINT @tmpstr
    SET @tmpstr = CHAR(9) + ‘CREATE LOGIN [‘ + @name + ‘] FROM WINDOWS’
    PRINT @tmpstr
    END
    END
    ELSE
    BEGIN — SQL Server authentication
    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

    IF (@binpwd IS NOT NULL)
    BEGIN — Non-null password
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=’ + @txtpwd + ‘ HASHED’
    END
    ELSE
    BEGIN — Null password
    SET @tmpstr = ‘CREATE LOGIN [‘ + @name + ‘] WITH PASSWORD=””’
    END

    SET @tmpstr = @tmpstr + ‘, CHECK_POLICY=OFF, SID=’ + @SID_string
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
    END

    IF @include_db = 1
    BEGIN
    PRINT ”
    PRINT ”
    PRINT ”
    PRINT ‘/***** SET DEFAULT DATABASES *****/’

    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

    WHILE @@fetch_status = 0
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr

    SET @tmpstr = ‘ALTER LOGIN [‘ + @name + ‘] WITH DEFAULT_DATABASE=[‘ + @dfltdb + ‘]’
    PRINT @tmpstr

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
    END
    END

    IF @include_role = 1
    BEGIN
    PRINT ”
    PRINT ”
    PRINT ”
    PRINT ‘/***** SET SERVER ROLES *****/’

    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

    WHILE @@fetch_status = 0
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr

    IF @xstatus &16 = 16 — sysadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”sysadmin”’
    PRINT @tmpstr
    END

    IF @xstatus &32 = 32 — securityadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”securityadmin”’
    PRINT @tmpstr
    END

    IF @xstatus &64 = 64 — serveradmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”serveradmin”’
    PRINT @tmpstr
    END

    IF @xstatus &128 = 128 — setupadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”setupadmin”’
    PRINT @tmpstr
    END

    IF @xstatus &256 = 256 –processadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”processadmin”’
    PRINT @tmpstr
    END

    IF @xstatus &512 = 512 — diskadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”diskadmin”’
    PRINT @tmpstr
    END

    IF @xstatus &1024 = 1024 — dbcreator
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”dbcreator”’
    PRINT @tmpstr
    END

    IF @xstatus &4096 = 4096 — bulkadmin
    BEGIN
    SET @tmpstr = ‘exec master.dbo.sp_addsrvrolemember @loginame=”’ + @name + ”’, @rolename=”bulkadmin”’
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
    END
    END

    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

    exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
    GO
    ————————————————————————————————————-
    — Code For Login Migrations From SQL Server 2005/2008 to SQL Server 2005/2008
    USE master
    GO
    IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = ‘0x’
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = ‘0123456789ABCDEF’
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint – (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    END

    SELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary varbinary (256)
    DECLARE @PWD_string varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name ‘sa’
    ELSE
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
    PRINT ‘No login(s) found.’
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = ‘/* sp_help_revlogin script ‘
    PRINT @tmpstr
    SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
    PRINT @tmpstr
    PRINT ”
    WHILE (@@fetch_status -1)
    BEGIN
    IF (@@fetch_status -2)
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

    SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
    — obtain password and sid
    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    — obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
    END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
    SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
    SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
    SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    ——————————————————————————-

  3. Thanks for the info! I had not thought of using SQL Server 2005. I ended-up using a script but, was going to try this if that did not work.

    Do you know if this would still work with an Express version of SS2005?

    Thanks,
    Charles

Leave a Reply to Charles Cancel reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.