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?
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.
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
——————————————————————————-
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
I’ve not tried this on 2005 Express, but I don’t see why it wouldn’t work there.
Pure gold. It worked perfectly. Thanks a bunch!