Перемещение имен входа и паролей между экземплярами SQL Server 2005

Переводы статьи Переводы статьи
Код статьи: 918992 - Vizualiza?i produsele pentru care se aplic? acest articol.
Развернуть все | Свернуть все

В этой статье

ВВЕДЕНИЕ

В этой статье описано, как перемещать имена входа и пароли между экземплярами Microsoft SQL Server 2005 на различных серверах.

Дополнительные сведения о перемещении имен входа и паролей между экземплярами других версий SQL Server см. в следующей статье базы знаний Майкрософт:
246133 Перемещение имен пользователей и паролей между экземплярами SQL Server

Дополнительная информация

В этой статье серверы A и B являются различными серверами. Как на сервере A, так и на сервере и B выполняется SQL Server 2005.

После перемещения базы данных с экземпляра SQL Server на сервере A в экземпляр SQL Server на сервере B, возможно, пользователи не смогут войти в базу данных на сервере B. Кроме того, может возникать следующее сообщение об ошибке:
Ошибка входа пользователя "пользователь". (Microsoft SQL Server, ошибка: 18456)
Эта проблема возникает из-за того, что имена входа и пароли не были перемещены из экземпляра SQL Server на сервере A в экземпляр SQL Server на сервере B.

Для перемещения имен входа и паролей выполните указанные ниже действия.
  1. На сервере A запустите SQL Server Management Studio и подключитесь к экземпляру SQL Server, из которого перемещена база данных.
  2. Откройте окно редактора запросов и выполните следующий сценарий:
    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 (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    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 @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary  varbinary (256)
    DECLARE @PWD_string  varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr  varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)
    
    DECLARE @defaultdb sysname
     
    IF (@login_name IS NULL)
      DECLARE login_curs CURSOR FOR
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
    ELSE
      DECLARE login_curs CURSOR FOR
    
    
          SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
    sys.server_principals p LEFT JOIN sys.syslogins l
          ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
    OPEN login_curs
    
    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
      PRINT 'Имена не найдены.'
      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 ''
    WHILE (@@fetch_status <> -1)
    BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
        PRINT ''
        SET @tmpstr = '-- Login: ' + @name
        PRINT @tmpstr
        IF (@type IN ( 'G', 'U'))
        BEGIN -- NT authenticated account/group
    
          SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
        END
        ELSE BEGIN -- SQL Server authentication
            -- obtain password and sid
                SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
            EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
            EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
     
            -- obtain password policy state
            SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
            SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
     
                SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
    
            IF ( @is_policy_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
            END
            IF ( @is_expiration_checked IS NOT NULL )
            BEGIN
              SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
            END
        END
        IF (@denylogin = 1)
        BEGIN -- login is denied access
          SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
        END
        ELSE IF (@hasaccess = 0)
        BEGIN -- login exists but does not have access
          SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
        END
        IF (@is_disabled = 1)
        BEGIN -- login is disabled
          SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
        END
        PRINT @tmpstr
      END
    
      FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
       END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO
    
    Примечание. Сценарий создает в базе данных master две хранимых процедуры — sp_hexadecimal и sp_help_revlogin.
  3. Выполните следующий оператор:
    EXEC sp_help_revlogin
    Сценарий, который создается хранимой процедурой sp_help_revlogin, является сценарием входа. Этот сценарий создает имена входа с исходным идентификатором (ИД) безопасности и паролем.
  4. На сервере B запустите SQL Server Management Studio и подключитесь к экземпляру SQL Server, на который перемещена база данных.

    Важно! Перед выполнением действия 5 прочтите раздел "Примечания".
  5. Откройте новое окно редактора запросов и выполните сценарий, созданный в действии 3.

Примечания

Перед выполнением выходного сценария в экземпляре на сервере B прочтите сведения, представленные ниже.
  • Внимательно просмотрите выходной сценарий. Если серверы A и B находятся в различных доменах, потребуется изменить его. В этом случае замените в операторах CREATE LOGIN исходное имя домена на новое имя домена. Так как интегрированные имена входа с правами доступа в новом домене будут иметь ИД безопасности, отличные от использовавшихся в исходном домене, пользователи будут изолированы от этих имен. Дополнительные сведения об устранении проблемы с изолированными пользователями см. в следующей статье базы знаний Майкрософт:
    240872 Устранение проблем, связанных с разрешениями, при перемещении базы данных на другой сервер Microsoft SQL Server
    Если серверы A и B находятся в одном и том же домене, используется одинаковый ИД безопасности. Поэтому, скорее всего, пользователи не будут изолированы.
  • В выходном сценарии имена входа создаются с зашифрованным паролем. Это объясняется наличием аргумента HASHED для оператора CREATE LOGIN. Этот аргумент указывает, что пароль, который вводится после аргумента PASSWORD, уже хэширован.
  • По умолчанию только члены с предопределенной ролью сервера sysadmin могут выполнять оператор SELECT из представления sys.server_principals. Пользователи могут создавать или выполнять конечный сценарий только в том случае, если член с предопределенной ролью сервера sysadmin предоставит им необходимые разрешения.
  • При выполнении действий, описанных в данной статье, сведения базы данных по умолчанию для определенного имени входа не перемещаются. Это происходит потому, что база данных по умолчанию может отсутствовать на сервере B. Чтобы определить базу данных по умолчанию для входа, используйте оператор ALTER LOGIN, указав имя входа и базу данных по умолчанию в качестве аргументов.
  • При сортировке на сервере A может не учитываться регистр, а на сервере B — учитываться. В этом случае после перемещения имен входа и паролей в экземпляр на сервере B пользователи должны вводить пароли полностью прописными буквами.

    Кроме того, возможно, при сортировке на сервере A учитывается регистр, а на сервере B — не учитываться. В этом случае пользователи не смогут входить в систему с именами и паролями, перемещенными в экземпляр на сервере B, если не выполняется одно из следующих условий:
    • исходные пароли не содержат букв;
    • все буквы в исходных паролях прописные.
    Если регистр учитывается или не учитывается при сортировке одновременно на обоих серверах, эта проблема не возникает.
  • Если имя входа, которое уже присутствует в экземпляре на сервере B, совпадает с именем в выходном сценарии, при выполнении сценария в экземпляре на сервере появится следующее сообщение об ошибке:
    Сооб. 15025, Уровень 16, Состояние 1, Строка 1
    Сервер-участник "пользователь" уже существует.
    Если имя входа, которое уже присутствует в экземпляре на сервере B, совпадает с ИД безопасности в выходном сценарии, при выполнении сценария в экземпляре на сервере появится следующее сообщение об ошибке:
    Сооб. 15433, Уровень 16, Состояние 1, Строка 1
    Указанный идентификатор безопасности параметра используется.
    Следовательно, необходимо выполнить указанные ниже действия.
    1. Внимательно просмотрите выходной сценарий.
    2. Просмотрите содержимое в представлении sys.server_principals в экземпляре на сервере B.
    3. Исправьте ошибки, соответствующие сообщениям.
  • В SQL Server 2005 на основе ИД безопасности имени входа осуществляется доступ на уровне базы данных. Имя входа может иметь два различных ИД безопасности для двух баз данных на сервере. В этом случае имя входа может использоваться только для входа в базу данных, ИД безопасности которой соответствует ИД безопасности в представлении sys.server_principals. Такая проблема возможна при консолидации двух баз данных с различных серверов. Для ее устранения вручную удалите имя входа из базы данных с несоответствующим ИД безопасности с помощью оператора DROP USER. После этого снова добавьте имя входа с помощью оператора CREATE USER.

Ссылки

Дополнительные сведения об устранении проблемы с изолированными пользователями см. на следующем веб-узле MSDN:
http://msdn2.microsoft.com/en-us/library/ms175475.aspx
Дополнительные сведения об операторе CREATE LOGIN см. на следующем веб-узле MSDN:
http://msdn2.microsoft.com/en-us/library/ms189751.aspx
Дополнительные сведения об операторе ALTER LOGIN см. на следующем веб-узле MSDN:
http://msdn2.microsoft.com/en-us/library/ms189828.aspx

Свойства

Код статьи: 918992 - Последний отзыв: 16 июля 2013 г. - Revision: 3.2
Информация в данной статье относится к следующим продуктам.
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Ключевые слова: 
kbsqlmanagementtools kbexpertiseadvanced kbhowto kbinfo KB918992

Отправить отзыв

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com