Results 1 to 5 of 5

Thread: Moving Logins

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: Moving Logins

    What is the best procedure to move database logins between servers ?
    DTS logins over? Should i move the logins first then the user databases or is it the other way around ?

  2. #2
    Join Date
    Jun 2004
    Posts
    4
    Not sure of the best way...

    Last time I did it, I did the following;

    Create a blank db of same name on destination Server.
    Used the transfer logins DTS
    Detached the blank DB
    Attached the Production DB
    Ran the sp_change_users_login for each transfered login.

    This worked a treat, however you need to run the SP for each user which could prove a pita if you have many logins.

    :@)

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Install these two scripts on the source database and EXEC sp_help_revlogin. You will then have a script to copy and run on the destination server. It will transfer the SIDs for you so you don't have to do any synching with sp_change_user_login.

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    /****** Object: Stored Procedure dbo.sp_help_revlogin Script Date: 6/21/2004 10:04:05 PM ******/
    ALTER PROCEDURE sp_help_revlogin @login_name
    sysname = NULL
    AS
    --Declare needed variables.
    DECLARE
    @name sysname,
    @xstatus int,
    @binpwd varbinary (256),
    @txtpwd sysname,
    @tmpstr varchar (256),
    @SID_varbinary varbinary(85),
    @SID_string varchar(256),
    @dbname varchar(255)
    --Determine whether to process one login or all. Set up cursor accordingly.
    IF (@login_name IS NULL)
    BEGIN
    DECLARE login_curs CURSOR FOR
    SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
    FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
    WHERE
    sxl.srvid IS NULL
    AND sxl.name <> 'sa'
    END
    ELSE
    BEGIN
    DECLARE login_curs CURSOR FOR
    SELECT
    sxl.sid,
    sxl.name,
    sxl.xstatus,
    sxl.password,
    sd.name AS dbname
    FROM
    master..sysxlogins sxl
    INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
    WHERE
    sxl.srvid IS NULL
    AND sxl.name <> @login_name
    END
    OPEN login_curs
    FETCH NEXT FROM login_curs
    INTO
    @SID_varbinary,
    @name,
    @xstatus,
    @binpwd,
    @dbname
    --If no logins found, exit the procedure.
    IF (@@fetch_status = -1)
    BEGIN
    PRINT 'No login(s) found.'
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SELECT @tmpstr = '/* sp_help_revlogin script '
    PRINT @tmpstr
    SELECT @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
    BEGIN
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
    END
    ELSE
    BEGIN
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
    END

    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
    BEGIN

    -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SET @tmpstr =
    'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
    END
    END
    IF (@xstatus & 2048) = 2048
    BEGIN
    -- login upgraded from 6.5
    SET @tmpstr = @tmpstr + '''skip_encryption_old'''
    PRINT @tmpstr
    END
    ELSE
    BEGIN
    SET @tmpstr = @tmpstr + '''skip_encryption'''
    PRINT @tmpstr
    END
    END
    --Add the default database.
    SET @tmpstr = 'EXEC master..sp_defaultdb ''' + @name + ''',''' + @dbname + ''''
    PRINT @tmpstr
    END
    FETCH NEXT FROM login_curs
    INTO
    @SID_varbinary,
    @name,
    @xstatus,
    @binpwd,
    @dbname
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0

    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    /****** Object: Stored Procedure dbo.sp_hexadecimal Script Date: 6/21/2004 10:05:58 PM ******/
    ALTER 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
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Jun 2004
    Posts
    4
    Dang!

    Yeah, forget to mention you have to run an SP on the source...

    Details can be found at..

    http://support.microsoft.com/default.aspx?kbid=246133#4

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Yes. You install both scripts in the master database on the source server. You execute sp_help_revlogin. You then run the permissions script it gives you on the destination. BTW, this version is a little bit different then the MS version. Be sure to include the sp_hex procedure also.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •