Solved

Transfer Logins from Old SQL server to New SQL server

Posted on 2014-03-13
10
595 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:Michael Wolfstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Michael Wolfstone
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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:Michael Wolfstone
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:Michael Wolfstone
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:Michael Wolfstone
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

628 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