Solved

Transfer Logins from Old SQL server to New SQL server

Posted on 2014-03-13
10
552 Views
Last Modified: 2014-03-20
I thought there was an easy way to transfer logins to the new server by exporting and loading, but I cannot find the SP that I think I used last time (sp_help_revlogin).
Can anyone suggest a simple method to do this.
I tried BCP out for syslogins but can't get the syntax correct.
0
Comment
Question by:BFOG
  • 6
  • 4
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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

Open in new window

0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
0
 

Author Comment

by:BFOG
Comment Utility
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?
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
0
 

Author Comment

by:BFOG
Comment Utility
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
0
Free book by J.Peter Bruzzese, Microsoft MVP

Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s free!

 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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...
0
 

Author Comment

by:BFOG
Comment Utility
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'
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
Comment Utility
The thing is, I think this procedure is designed to work with SQL Server logins, not Windows logins.  I don't know how it will work with Windows logins, but I suspect it won't - since they are explicitly excluded.  If you want to try it, try it with just one.  Pick one of the names from the result of the query above (which may include a domainName/loginName) and pass that as a parameter to the modified procedure.
0
 

Author Closing Comment

by:BFOG
Comment Utility
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.
0

Featured Post

The problems with reply email signatures

Do you wish that you could place an email signature under a reply? Well, unfortunately, you can't. That great Exchange/Office 365 signature you've created will just appear at the bottom of an email chain. What a pain! Is there really no way to solve this? Well, there might be...

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now