Transfer Logins from Old SQL server to New SQL server

Here is the sp_help_revlogin procedure.  Call it with no parameters to get a script with all logins:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_help_revlogin]    Script Date: 03/13/2014 15:50:09 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL AS
BEGIN

  DECLARE @name    sysname
  DECLARE @status  smallint
  DECLARE @txtpwd  sysname
  DECLARE @binpwd  varbinary(256)
  DECLARE @tmpstr  varchar (256)
  DECLARE @SID_varbinary varbinary(256)
  DECLARE @SID_string varchar(256)

  IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR 
      SELECT sid, name, status, password 
      FROM master..syslogins 
      WHERE name <> 'sa' and name not like '##MS_%' and isntname=0
      ORDER BY name
  ELSE
    DECLARE login_curs CURSOR FOR 
      SELECT sid, name, status, password 
      FROM master..syslogins 
      WHERE name like @login_name  and  name not like '##MS_%'  and  isntname=0
      ORDER BY name

  OPEN login_curs 

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @status, @txtpwd

  IF (@@fetch_status = -1)
  BEGIN
    --PRINT 'No login(s) found.'
    PRINT 'PRINT ''-- No login(s) found: ' + isnull(@login_name,'') + ''''
    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
      SET @tmpstr = 'IF NOT EXISTS(SELECT sid FROM master..syslogins WHERE name=''' + @name + ''')'
      PRINT @tmpstr
      PRINT 'BEGIN -- only if login does not already exist'
      PRINT ' PRINT ''-- adding login: ' + @name + ''''
      IF (@status & 4) = 4
      BEGIN -- NT authenticated account/group
        IF ( @status & 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 (@txtpwd IS NOT NULL)
        BEGIN -- Non-null password
          SELECT @binpwd = CONVERT(varbinary(256),@txtpwd)
          EXEC sp_hexadecimal @binpwd, @txtpwd OUT
          IF (@status & 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 ( @status & 2048) = 2048
          -- login upgraded from 6.5
          SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
        ELSE 
          SET @tmpstr = @tmpstr + '''skip_encryption'''
        PRINT @tmpstr
        SET @tmpstr = ' ALTER LOGIN [' + @name +'] WITH CHECK_POLICY = OFF;' 
        PRINT @tmpstr
      END
      PRINT 'END'
      PRINT 'ELSE PRINT ''-- ' + @name + ' already exists!'''
    END
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name,  @status, @txtpwd
  END -- while

  CLOSE login_curs 
  DEALLOCATE login_curs 
  PRINT 'GO'
  PRINT ''
  RETURN 0;

END -- sp_help_revlogin
GO

Select allOpen in new window

Note that this version has been slightly enhanced to accept the % wildcard character in the @login_name parameter, so if you have three logins: 'yyz1000a' , 'yyz1000b' , 'yyz1000c'
then this will generate a script for all three:

  exec master..sp_help_revlogin 'yyz1000%'

But to generate a script for all logins, don't pass anything in the param:

  exec master..sp_help_revlogin

The script parses with no errors; the sp is created with no errors
When I exec it with no parameters I get the following (every time)

/* sp_help_revlogin script
** Generated Mar 14 2014  9:46AM on VSQL */
 
DECLARE @pwd sysname
IF NOT EXISTS(SELECT sid FROM master..syslogins WHERE name='retailmas')
BEGIN -- only if login does not already exist
 PRINT '-- adding login: retailmas'
 SET @pwd = CONVERT (varbinary(256), 0x01004DFDDA1BBC36256EBF30E9D89DF4D1F42E46CB1DF2DC8D77)
 EXEC master..sp_addlogin 'retailmas', @pwd, @sid = 0x98A0037D2529F245B0A511188D841E2A, @encryptopt = 'skip_encryption'
 ALTER LOGIN [retailmas] WITH CHECK_POLICY = OFF;
END
ELSE PRINT '-- retailmas already exists!'
GO

retailmas is a valid user name but I only get the above.
Your thoughts?

What is the result of this query?

SELECT count(*) -- sid, name, status, password
FROM master..syslogins
WHERE name <> 'sa' and name not like '##MS_%' and isntname=0
ORDER BY name

I ran the following (to get a clean parse and exec)

use master
go

SELECT count(*) -- sid, name, status, password
FROM master..syslogins
WHERE name <> 'sa' and name not like '##MS_%' and isntname=0
-- ORDER BY name

Result = 1

hmmm ... that suggests you have only one login on this instance.  Uncomment the following lines one at a time, and compare the results:

select *
FROM master..syslogins
--WHERE name <> 'sa'
--  and name not like '##MS_%'
--  and isntname=0

ah-ha, that might be it ... are your logins NT (Windows) logins instead of SQL logins?

If so, then that last param above is the key...

With NO where I get 143
With where not sa I get 142
With where not sa and name not... I get 138
With Isntname =0 I get 1

I am guessing now that I should remove a reference to 'isntname = 0'

The information about SQL logins seems to be correct.  I tried extracting Windows logins,  scripting the extracted data and loading into the new SQL.  It seems to work so far.