Как перенести имена пользователей и пароли с одного SQL Server 2005 на другой SQL Server 2005?

Top  Previous  Next

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

Описание задачи:

Перенести имена пользователей и пароли с одного сервера на другой. На обоих серверах выполняется SQL Server 2005.

Для решения данной задачи необходимо следовать следующему алгоритму:

1.На сервере A с помощью программы «Администратор MSDE» подключиться к экземпляру SQL Server, из которого перемещена база данных (при наличии установленного Microsoft SQL Server можно воспользоваться SQL Server Management Studio (или SQL Manager).

2.Открыть окно редактора запросов и выполнить следующий сценарий:

 

Внимание: для корректного выполнения сценария необходимо сначала скопировать текст сценария из данного руководства в программу WordPad, затем из программы WordPad скопировать текст в программу Блокнот.

 

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 'Eiaia ia iaeaaiu.'

  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 запустите программу «Администратор MSDE» (при наличии установленного Microsoft SQL Server можно воспользоваться SQL Server Management Studio (или SQL Manager)) и выполните подключение к экземпляру SQL Server, на который перемещена база данных.

Внимание: перед выполнением действия 5 прочтите блок примечаний ниже.

5.Открыть новое окно редактора запросов и выполнить сценарий, созданный в действии 3.

Примечания

Перед выполнением выходного сценария в экземпляре на сервере B необходимо прочитать сведения, представленные ниже.

1.Внимательно просмотрите выходной сценарий. Если серверы A и B находятся в различных доменах, потребуется изменить его. В этом случае замените в операторах CREATE LOGIN исходное имя домена на новое имя домена. Так как интегрированные имена входа с правами доступа в новом домене будут иметь ИД безопасности, отличные от использовавшихся в исходном домене, пользователи будут изолированы от этих имен. Дополнительные сведения об устранении проблемы с изолированными пользователями см. в статье базы знаний Майкрософт (http://support.microsoft.com/kb/240872/) Если серверы A и B находятся в одном и том же домене, используется одинаковый ИД безопасности. Поэтому, скорее всего, пользователи не будут изолированы.

2.В выходном сценарии имена входа создаются с зашифрованным паролем. Это объясняется наличием аргумента HASHED для оператора CREATE LOGIN. Этот аргумент указывает, что пароль, который вводится после аргумента PASSWORD, уже хэширован.

3.По умолчанию только члены с предопределенной ролью сервера sysadmin могут выполнять оператор SELECT из представления sys.server_principals. Пользователи могут создавать или выполнять конечный сценарий только в том случае, если член с предопределенной ролью сервера sysadmin предоставит им необходимые разрешения.

4.При выполнении действий, описанных в данной статье, сведения базы данных по умолчанию для определенного имени входа не перемещаются. Это происходит потому, что база данных по умолчанию может отсутствовать на сервере B. Чтобы определить базу данных по умолчанию для входа, используйте оператор ALTER LOGIN, указав имя входа и базу данных по умолчанию в качестве аргументов.

5.При сортировке на сервере A может не учитываться регистр, а на сервере B — учитываться. В этом случае после перемещения имен входа и паролей в экземпляр на сервере B пользователи должны вводить пароли полностью прописными буквами.

6.Кроме того, возможно, при сортировке на сервере A учитывается регистр, а на сервере B — не учитывается. В этом случае пользователи не смогут входить в систему с именами и паролями, перемещенными в экземпляр на сервере B, если не выполняется одно из следующих условий:

исходные пароли не содержат букв;

все буквы в исходных паролях прописные.

Если регистр учитывается или не учитывается при сортировке одновременно на обоих серверах, эта проблема не возникает.

7.Если имя входа, которое уже присутствует в экземпляре на сервере B, совпадает с именем в выходном сценарии, при выполнении сценария в экземпляре на сервере появится следующее сообщение об ошибке:

Сооб. 15025, Уровень 16, Состояние 1, Строка 1
Сервер-участник "пользователь" уже существует.

Если имя входа, которое уже присутствует в экземпляре на сервере B, совпадает с ИД безопасности в выходном сценарии, при выполнении сценария в экземпляре на сервере появится следующее сообщение об ошибке:

Сооб. 15433, Уровень 16, Состояние 1, Строка 1
Указанный идентификатор безопасности параметра используется.

Следовательно, необходимо выполнить указанные ниже действия.

Внимательно просмотрите выходной сценарий.

Просмотрите содержимое в представлении sys.server_principals в экземпляре на сервере B.

Исправьте ошибки, соответствующие сообщениям.

8.В SQL Server 2005 на основе ИД безопасности имени входа осуществляется доступ на уровне базы данных. Имя входа может иметь два различных ИД безопасности для двух баз данных на сервере. В этом случае имя входа может использоваться только для входа в базу данных, ИД безопасности которой соответствует ИД безопасности в представлении sys.server_principals. Такая проблема возможна при консолидации двух баз данных с различных серверов. Для ее устранения вручную удалите имя входа из базы данных с несоответствующим ИД безопасности с помощью оператора DROP USER. После этого снова добавьте имя входа с помощью оператора CREATE USER.

При написании данного раздела использовался материал статьи базы знаний Майкрософт: http://support.microsoft.com/kb/918992/.


    © 2017 ООО «ИТРИУМ СПб»
    Вопросы и предложения
    www.itrium.ru