How to transfer logins and passwords between instances of SQL Server that are running older versions of SQL Server

Summary

Note To transfer logins between instances that are running SQL Server 2005 or a later version, see How to transfer logins and passwords between instances of SQL Server.
After you move databases to a new server, users may not be able to log in to the new server. Instead, they receive the following error message:
Msg 18456, Level 16, State 1
Login failed for user '%ls'.
You must transfer the logins and passwords to the new server. This article describes how you transfer logins and passwords to a new server.

How to transfer logins and passwords between servers that are running SQL Server 7.0

The SQL Server 7.0 Data Transformation Services (DTS) Object Transfer feature transfers logins and users between two servers, but it does not transfer the passwords for SQL Server authenticated logins. To transfer logins and passwords from a server that is running SQL Server 7.0 to another server that is running SQL Server 7.0, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

How to transfer logins and passwords from SQL Server 7.0 to SQL Server 2000 or between servers that are running SQL Server 2000

To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps:
  1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
  2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.

    Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.

    Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

How to transfer logins and passwords between instances of SQL Server 2005

For more information about how to transfer the logins and passwords between instances of SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:

918992 How to transfer the logins and the passwords between instances of SQL Server 2005

A complete resolution to transfer logins and passwords between different versions of SQL Server

To do this, use one of the following methods.
Notes
  • The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
  • The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
  • Review the remarks at the end of this article for important information about the steps in the methods.
  • Method 2 assigns logins to roles.

Method 1

This method applies to the following scenarios:
  • You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
  • You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
  • You transfer logins and passwords between servers that are running SQL Server 2000.
To transfer logins and passwords between different versions of SQL Server, follow these steps:
  1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the
    sp_help_revlogin stored procedure.
    ----- Begin Script, Create sp_help_revlogin procedure -----

    USE master
    GO
    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_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
    ----- End Script -----

  2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:
    EXEC master..sp_help_revlogin

Method 2

This method applies to the following scenarios:
  • You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
  • You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
  • You assign logins to roles.
To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:
  1. Run the following script on the source SQL Server.
    USE master 
    GO
    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_2000_to_2005') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin_2000_to_2005
    GO
    CREATE PROCEDURE sp_help_revlogin_2000_to_2005

    @login_name sysname = NULL,
    @include_db bit = 0,
    @include_role bit = 0

    AS
    DECLARE @name sysname
    DECLARE @xstatus int
    DECLARE @binpwd varbinary (256)
    DECLARE @dfltdb varchar (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 STATIC FOR
    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
    FROM master.dbo.sysxlogins
    WHERE srvid IS NULL AND
    [name] <> 'sa'
    ELSE
    DECLARE login_curs CURSOR FOR
    SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
    FROM master.dbo.sysxlogins
    WHERE srvid IS NULL AND
    [name] = @login_name

    OPEN login_curs

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

    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 ''
    PRINT ''
    PRINT '/***** CREATE LOGINS *****/'

    WHILE @@fetch_status = 0
    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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
    PRINT @tmpstr
    SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
    PRINT @tmpstr
    END
    END
    ELSE
    BEGIN -- SQL Server authentication
    EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

    IF (@binpwd IS NOT NULL)
    BEGIN -- Non-null password
    EXEC sp_hexadecimal @binpwd, @txtpwd OUT
    SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
    END
    ELSE
    BEGIN -- Null password
    SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
    END

    SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
    PRINT @tmpstr
    END

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

    IF @include_db = 1
    BEGIN
    PRINT ''
    PRINT ''
    PRINT ''
    PRINT '/***** SET DEFAULT DATABASES *****/'

    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

    WHILE @@fetch_status = 0
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr

    SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
    PRINT @tmpstr

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

    IF @include_role = 1
    BEGIN
    PRINT ''
    PRINT ''
    PRINT ''
    PRINT '/***** SET SERVER ROLES *****/'

    FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb

    WHILE @@fetch_status = 0
    BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr

    IF @xstatus &16 = 16 -- sysadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
    PRINT @tmpstr
    END

    IF @xstatus &32 = 32 -- securityadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
    PRINT @tmpstr
    END

    IF @xstatus &64 = 64 -- serveradmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
    PRINT @tmpstr
    END

    IF @xstatus &128 = 128 -- setupadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
    PRINT @tmpstr
    END

    IF @xstatus &256 = 256 --processadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
    PRINT @tmpstr
    END

    IF @xstatus &512 = 512 -- diskadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
    PRINT @tmpstr
    END

    IF @xstatus &1024 = 1024 -- dbcreator
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
    PRINT @tmpstr
    END

    IF @xstatus &4096 = 4096 -- bulkadmin
    BEGIN
    SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
    PRINT @tmpstr
    END

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

    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

    exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
    GO
  2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.
Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following.
CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID

Remarks

  • Review the output script carefully before you run it on the destination SQL Server. If you have to transfer logins to an instance of SQL Server in a different domain than the source instance of SQL Server, edit the script generated by the sp_help_revlogin procedure, and replace the domain name with the new domain in the
    sp_grantlogin statements. Because the integrated logins granted access in the new domain will not have the same SID as the logins in the original domain, the database users will be orphaned from these logins. To resolve these orphaned users, see the articles referenced in the following bullet item. If you transfer integrated logins between instances of SQL Servers in the same domain, the same SID is used and the user is not likely to be orphaned.
  • After you move the logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
    Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
    For instructions about how to map the logins to the database users to resolve orphaned SQL Server logins and integrated logins, see the following article in the Microsoft Knowledge Base:
    240872 How to resolve permission issues when you move a database between servers that are running SQL Server

    For instructions about using the sp_change_users_login stored procedure to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), see the following article in the Microsoft Knowledge Base:
    274188 "Troubleshooting Orphaned Users" topic in Books Online is incomplete

  • If the transfer of logins and passwords is part of a move of databases to a new server running SQL Server, see the following article in the Microsoft Knowledge Base for a description of the workflow and steps involved:
    314546 How to move databases between computers that are running SQL Server

  • You can do this because of the @encryptopt parameter in the sp_addlogin system stored procedure, that allows a login to be created by using the encrypted password. For more information about this procedure, see the "sp_addlogin (T-SQL)" topic in SQL Server Books Online.
  • By default, only members of the sysadminfixed server role can select from the sysxlogins table. Unless a member of the sysadmin role grants the necessary permissions, end users cannot create or run these stored procedures.
  • This approach does not try to transfer the default database information for a particular login because the default database may not always exist on the destination server. To define the default database for a login, you can use the sp_defaultdb system stored procedure by passing it the login name and the default database as arguments. For more information about using this procedure, see the "sp_defaultdb" topic in SQL Server Books Online.
  • During a transfer of logins between instances of SQL Server, if the sort order of the source server is case-insensitive and the sort order of the destination server is case-sensitive, you must enter all alphabetical characters in passwords as uppercase characters after the transfer of logins to the destination server. If the sort order of the source server is case-sensitive and the sort order of the destination server is case-insensitive, you will not be able to log in with the logins transferred using the procedure outlined in this article, unless the original password contains no alphabetical characters or unless all alphabetical characters in the original password are uppercase characters. If both servers are case-sensitive or both servers are case-insensitive, you will not experience this problem. This is a side effect of the way that SQL Server handles passwords. For more information, see the "Effect on Passwords of Changing Sort Orders" topic in SQL Server 7.0 Books Online.
  • When you run the output from the sp_help_revlogin script on the destination server, if the destination server already has a login defined with the same name as one of the logins on the script output, you may see the following error upon execution of the output of the sp_help_revlogin script:
    Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
    The login 'test1' already exists.
    Likewise, if a different login exists with the same SID value on this server as the one you are trying to add, you receive the following error message:
    Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
    Supplied parameter @sid is in use.
    Therefore, you must carefully review the output from these commands, examine the contents of the sysxlogins table, and address these errors accordingly.
  • The SID value for a particular login is used as the basis for implementing database level access in SQL Server. Therefore, if the same login has two different values for the SID at the database level (in two different databases on that server), the login will only have access to that database whose SID matches the value in syslogins for that login. Such a situation might occur if the two databases in question have been consolidated from two different servers. To resolve this problem, the login in question would have to be manually removed from the database that has a SID mismatch by using the sp_dropuser stored procedure, and then added again by using the sp_adduser stored procedure.
Propriedades

ID do Artigo: 246133 - Última Revisão: 24/01/2017 - Revisão: 4

Comentários