SS 2005 - migrating logins from one server to another

  • Does anyone have a script to migrate logins, passwords, permissions, ... from one SS 2005 server to another?

    Thanks.

  • you could use ssis (dts) it has a task to do this.

  • I can't recall where I got these SPs but I've been using sp_help_revlogin to generate a script that can recreate logins on another SQL server.  You need to run both scripts to create sp_hexadecimal and sp_help_reglogin before you can execute sp_help_revlogin.  I have created scripts to be run on SQL2000 servers and SQL2005 servers.

    --*****begin sp_hexadecimal*****

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_hexadecimal] GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar(256) OUTPUT

    AS

    DECLARE @charvalue varchar(256)

    DECLARE @i int

    DECLARE @length int

    DECLARE @hexstring char(16)

    SELECT @charvalue = '0x'

    SELECT @i = 1

    SELECT @length = DATALENGTH (@binvalue)

    SELECT @hexstring = '0123456789ABCDEF'

    WHILE (@i <= @length)

    BEGIN

    DECLARE @tempint int

    DECLARE @firstint int

    DECLARE @secondint int

    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

    SELECT @firstint = FLOOR(@tempint/16)

    SELECT @secondint = @tempint - (@firstint*16)

    SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

    SELECT @i = @i + 1

    END

    SELECT @hexvalue = @charvalue

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    --****end sp_hexadecimal*****

    --*****begin sp_help_revlogin*****

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_help_revlogin] GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

    DECLARE @name sysname

    DECLARE @xstatus int

    DECLARE @binpwd varbinary (256)

    DECLARE @txtpwd sysname

    DECLARE @tmpstr varchar (256)

    DECLARE @SID_varbinary varbinary(85)

    DECLARE @SID_string varchar(256)

    IF (@login_name IS NULL)

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name <> 'sa'

    ELSE

    DECLARE login_curs CURSOR FOR

    SELECT sid, name, xstatus, password FROM master..sysxlogins

    WHERE srvid IS NULL AND name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd IF (@@fetch_status = -1) BEGIN

    PRINT 'No login(s) found.'

    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

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@xstatus & 4) = 4

    BEGIN -- NT authenticated account/group

    IF (@xstatus & 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 (@binpwd IS NOT NULL)

    BEGIN -- Non-null password

    EXEC sp_hexadecimal @binpwd, @txtpwd OUT

    IF (@xstatus & 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 (@xstatus & 2048) = 2048

    -- login upgraded from 6.5

    SET @tmpstr = @tmpstr + '''skip_encryption_old'''

    ELSE

    SET @tmpstr = @tmpstr + '''skip_encryption'''

    PRINT @tmpstr

    END

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    --*****end sp_help_revlogin*****

  • Ryan, thank you, but this will not work in SS 2005.

    As far as the SSIS method, do you know offhand a way to exclude specific logins?  I can see a way to do it by db or by only do selected logins, but I'm looking for a way to automate moving any newly created or changed logins to another server on a nightly basis, but 3 specific logins can not be migrated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply