Solved

Transfer Logins from Old SQL server to New SQL server

Posted on 2014-03-13
10
572 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
ID: 39927490
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
ID: 39927503
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
ID: 39929835
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 33

Expert Comment

by:knightEknight
ID: 39929919
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
ID: 39929938
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 39930064
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
ID: 39930067
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
ID: 39930603
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
ID: 39930882
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
ID: 39943456
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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