Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Move / copy sql 2000 user login to sql 2005

    Good day ,
    Does anyone know how to moe / copy database users from sql server 2000 to sql server 2005. I ave been successful will everything except this in my migration. I can copy the database users themselves but not the users under the security section.

    Please help

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How are you moving the database(s) so far? if you are doing this by backup/restore, then all you would need to do is create the corresponding logins on the SQL 2005 server. Look up "create login" in the SQL 2005 BOL, and pay attention to the SID parameter, as you will need to re-use the SID from SQL 2000. If you need to bring over the passwords for SQL Server logins, you can use the HASHED option, which is similar to the old skip_encryption in SQL 2000 sp_addlogin.

  3. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    I have detached the database from 2000, then copied the mdf and ldf files to the new 2005 server and reattched it. The users for each database are there but the users under secruity then logins for the server itself are not there and I can find no way to copy them to the new server. Yes I can create them there but not all the passwords are known to me and hence why I need to automate the action...

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you need to bring over the passwords for SQL Server logins, you can use the HASHED option, which is similar to the old skip_encryption in SQL 2000 sp_addlogin.
    Go ahead and look up sp_addlogin to see how to get the hashed password from SQL 2000.

  5. #5
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    Here is your answer:
    You Need to change MTL_DMTEST3 to your source server. then execute this code against your source server this will generate in QA something like this:

    DECLARE @pwd sysname

    SET @pwd = CONVERT (varbinary(256), 0x01003F1E1D12DA42CD6249BFC77F5A2E4B5E620E15768BFF 24F5E2E81027AB0A35E7E3B26F0D7D0EEFD7E5A35E21)
    EXEC master..sp_addlogin 'AB', @pwd, @sid = 0x8C540536DC4E624CBA88631CB1E664CA, @encryptopt = 'skip_encryption'

    SET @pwd = CONVERT (varbinary(256), 0x0100197BED2BA1D42FA9DDF5C47F690D62FDFCDDE54EE101 EBDE97387571F0DD6016FE952759C7AEC62C108B4971)
    EXEC master..sp_addlogin 'CD', @pwd, @sid = 0x1D03A0CA55DE0A4593DC01D918D42BA0, @encryptopt = 'skip_encryption'

    SET @pwd = CONVERT (varbinary(256), 0x0100FF1F263754B14609BB46C341338FED184721E712A5AF 4DD475A4F889B34BC794DD573B1B2E7A7B3E247AD50A)
    EXEC master..sp_addlogin 'EF', @pwd, @sid = 0xD5805F3620F4F74FA1C510D75A9F44BE, @encryptopt = 'skip_encryption'

    SET @pwd = CONVERT (varbinary(256), 0x0100AB393558292C8FFCE4D75B5C7AE46AA0983AED7037B3 B2FD939FF9A38E689BAFB0F7FCFFAF88F3CCCEE791A2)
    EXEC master..sp_addlogin 'GH', @pwd, @sid = 0x7BA1477E9B9A8D45972F83ED13A68451, @encryptopt = 'skip_encryption'

    grab the code above and run in sql QA on your target

    Here is the code you need to run on your source to generate the code above
    ----- Begin Script, Create sp_help_revlogin procedure -----
    USE master
    GO
    SET NOCOUNT ON
    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    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

    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_MY_DBLogins @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 a.sid, a.name, a.xstatus, a.password FROM
    master..sysxlogins a
    join MTL_DMTEST3..sysusers MY_DB on a.sid = MY_DB.sid
    WHERE a.srvid IS NULL AND a.name <> 'sa'

    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT a.sid, a.name, a.xstatus, a.password FROM
    master..sysxlogins a
    join MTL_DMTEST3..sysusers MY_DB on a.sid = MY_DB.sid
    WHERE a.srvid IS NULL AND a.name <> 'sa'


    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
    PRINT ''
    PRINT 'DECLARE @pwd sysname'
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ''
    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
    ----- End Script -----
    --
    EXEC master..sp_help_MY_DBLogins
    --drop proc sp_help_MY_DBLogins

Posting Permissions

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