Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Transfer Logins from Old SQL server to New SQL server

Posted on 2014-03-13
10
Medium Priority
?
608 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 1500 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 IT Pros

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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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