Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008

UPDATE: Just use my PowerShell script, Copy-SqlLogin. You just gotta execute one line and DBA’s love it.

To my knowledge, Microsoft has not yet updated their Knowledge Base to inform administrators how to migrate SQL Server logins to SQL Server 2008. The good news is that the SQL Server 2005 login migration knowledge base article applies to SQL Server 2008 as well. Below I’ve included a compilation of all of the migration procedures between various versions of SQL Server.

Migration is very easy — basically, a stored procedure named sp_help_revlogin is created and executed on the source server. The resulting output is then copied and executed on the destination server.

Detailed instructions can be found at Microsoft KB Article 918992 and Microsoft KB Article 246133. Oh, also, Dell has a marvelous 54-page SQL Server 2000 to 2008 Migration Guide. It actually outlines, in an easy-to-read style, a lot of the best practices I learned about while studying for my MCITP certification.

Code For Login Migrations Between SQL Server 7 and SQL Server 2000

—– Begin Script, Create sp_help_revlogin procedure —–

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

Mmm, hella swole code.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server
15 comments on “Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008
  1. Tyler Holmes says:

    Nice moves. Very helpful post.

  2. Stephen says:

    I love this stored procedure, makes moving databases around servers so much easier!

  3. nanda says:

    This is really cool procedure. Thanks a lot for sharing!

  4. Rx says:

    Great code.
    It seems to work on migrating from SQL 2005 to SQL 2008. Still testing more in-depth.
    2 SP's – was wondering if there was an order to run?

  5. Jackie says:

    This still does not work for extracting SQL 2008 logins to move to another SLQ 2008 server though..Do you one that does? I have been looking everywhere. Thanks.

  6. John Pether says:

    This worked for me, 2008 to 2008 hope it helps!!
    <code>
    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,
    @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], is_disabled, password_hash, isnull(default_database_name, 'master')
    FROM master.sys.sql_logins
    WHERE credential_id IS NULL AND
    [name] 'sa' AND [name] NOT LIKE '##%'
    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT sid, [name], is_disabled, password_hash, isnull(default_database_name, 'master')
    FROM master.sys.sql_logins
    WHERE credential_id 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 @login_name=NULL, @include_db=1, @include_role=1
    GO

    </code>

  7. dennis says:

    Thanks for posting! It was very helpfull.

  8. gene says:

    Excellent code, real perl!

  9. jayant Dass says:

    Hi All,

    You can try below script also which is very simple

    SET NOCOUNT ON
    SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
    ,', @defdb = ''' + dbname + ''''
    ,', @deflanguage = ''' + language + ''''
    ,', @encryptopt = ''skip_encryption'''
    ,', @passwd ='
    , cast(password AS varbinary(256))
    ,', @sid ='
    , sid
    FROM syslogins
    WHERE name NOT IN ('sa')
    AND isntname = 0

    Thanks & Regards
    Jayant dass
    [email protected]

  10. George says:

    I've tried this and every other process to move my logins and passwords from 2000 to 2008. I've even tried going to 2005 first. Nothing seems to work for the passwords. The logins come in fine, but the passwords do not. Any suggestions?

  11. Acchu says:

    hi! Anyway we can do it in reverse order, i am trying to copy users from sql 2008 to 2000,

  12. SZH says:

    hi!.. I found that above script did not work if user id is included '.' (Jason.Ong) or any special character.

    I am trying to find in everywhere to fix this issue.. Please help

  13. SZH says:

    hi!.. I found that above script did not work if user id is included '.' (Jason.Ong) or any special character.

    I am trying to find in everywhere to fix this issue.. Please help!!

  14. Santosh Deshar says:

    Hello,
    I am working on migrating databases from sql server 2008 to 2012. Will it be the same scripts to migrate logins and passwords. I am actually using backup and restore procedure to move my databases from 2008 to 2012, just worried about not having all the logins copied over.
    Thank you.

    • Chrissy LeMaire says:

      This post is old and outdated – try PowerShell. Copy-DbaLogin in the dbatools toolset will do all of that for you 👍

1 Pings/Trackbacks for "Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008"
  1. […] In order to transfer users to a new server, you need a sp_help_revlogin stored procedure. Depending on the version of your SQL Server (7/2000/2005/2008), you need a slightly different version of that script. All versions are available at NetNerds.net. […]

Leave a Reply