Передача логинов и паролей между SQL серверами

http://www.sql.ru/articles/mssql/02080601transferloginsandpasswordsbetweensqlservers.shtml

По материалам статьи Microsoft: INF: How To Transfer Logins and Passwords Between SQL Servers (Q246133)
СОДЕРЖАНИЕ

1.	Введение
2.	Передача логинов и паролей между серверами SQL Server 7.0
3.	Передача логинов и паролей между серверами SQL Server 7.0 и SQL Server 2000 или между SQL Server 2000 серверами
4.	Скрипт перемещения логинов с паролями и оригинальными SID между SQL серверами
5.	Рекомендации

Введение
Информация в этой статье относится к Microsoft SQL Server 7.0/2000 (все издания)
После перемещения базы данных в другой сервер, пользователи прежнего SQL сервера не смогут подключиться к новому серверу. Это происходит потому, что на новом сервере отсутствуют логины этих пользователей и их необходимо восполнить. В статье Microsoft Q246133 предлагается решение, которое позволяет упростить процедуру передачи логинов на другой сервер и описываются наиболее типичные проблемы, которые при этом могут возникнуть.
Если после перемещения базы данных на другой сервер Вы получите следующее сообщение об ошибке:
   Msg 18456, Level 16, State 1
   Login failed for user '%ls'.
Вы должны передать логины и пароли на новый сервер.
[Содержание]
Передача логинов и паролей между серверами SQL Server 7.0
SQL Server 7.0 Data Transformation Services (DTS) Object Transfer позволяет передавать логины и пользователей между двумя серверами, но не предусматривает передачу паролей для аутентифицированных SQL Server логинов. Чтобы передавать логины и пароли одного SQL Server 7.0 на другой, можно использовать представленную ниже хранимую процедуру sp_help_revlogin. Эта процедура создаёт сценарий, который может быть выполнен на новом сервере, где будут созданы логины с правильными SID и с установленными, прежними паролями.
[Содержание]
Передача логинов и паролей между серверами SQL Server 7.0 и SQL Server 2000 или между SQL Server 2000 серверами
Чтобы передать логины и пароли SQL Server 7.0 на какой-нибудь экземпляр SQL Server 2000, или между двумя экземплярами SQL Server 2000, можно использовать новый DTS Package Transfer Logins Task, входящий в комплект SQL Server 2000. Для этого нужно:

1. Подключитесь к SQL Server 2000, на который необходимо перенести логины с паролями, и используйте Data Transformation Services входящий в поставку SQL Server Enterprise Manager, разверните папку с таким же именем в дереве нового сервера и щёлкните правой кнопкой мыши по Local Packages, а затем выберете New Package.
2. После того, как появиться окно мастера DTS, щёлкните по Transfer Logins Task из меню Task. Укажите необходимую информацию о серверах во вкладках Source, Destination и Logins. Если Вы импортируете логины из SQL сервера, который находится на другом компьютере, необходимо, что бы экземпляр нового SQL сервера запускался из под учётной записи домена.

ОБРАТИТЕ ВНИМАНИЕ: Вы можете использовать метод с DTS или представленный ниже сценарий для передачи логинов между SQL Server 7.0 и SQL Server 2000, или между разными экземплярами SQL Server 2000. Метод DTS может передать пароли, но не может передать оригинальные SID. Если логин будет создан с отличным от оригинала SID и пользовательские базы данных также будут перемещены на новый сервер, связь между пользователями базы данных и её логинами будет утеряна. Для передачи оригиналов SID и предотвращения утери связи между пользователями и логинами, используйте представленный ниже сценарий вместо метода DTS.
[Содержание]
Скрипт перемещения логинов с паролями и оригинальными SID между SQL серверами
До применения представленного в этой главе скрипта, ознакомьтесь с находящимися в конце статьи рекомендациями, которые представляют важные замечания и дополнения к способам применения предлагаемых в настоящей статье способов переноса логинов, паролей и SID между SQL серверами.

1. Выполните представленный ниже скрипт на SQL сервере с которого необходимо перенести логины. Этот скрипт создаёт два хранимых процедуры с именами sp_hexadecimal и sp_help_revlogin в системной базе данных master. После успешного исполнения скрипта, выполните операции из пункта 2.
   ОБРАТИТЕ ВНИМАНИЕ: Создаваемые в процессе исполнения скрипта процедуры напрямую оперируют с системными таблицами SQL Server. Структура этих таблиц может изменяться от версии к версии SQL Server, что может повлиять на работоспособность этого скрипта.
   

   
   ----- 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. После того, как будет создана хранимая процедура sp_help_revlogin, запустите эту процедуру в Query Analyzer на исходном сервере:
   

   
      EXEC master..sp_help_revlogin

   Хранимая процедура sp_help_revlogin может использоваться и на SQL Server 7.0 и на SQL Server 2000. Результат, выводимый sp_help_revlogin, представляет собой готовый скрипт, который создаёт логины с оригинальными SID и паролям. Сохраните выведенный в окно результатов исполнения скрипта текст, и затем выполните его как скрипт в Query Analyzer на том SQL сервере, куда необходимо перенести логины

[Содержание]
Рекомендации

1. Внимательно проанализируйте создаваемый процедурой скрипт прежде, чем запустить его на SQL сервере, куда необходимо передать логины. Если Вы должны передать NT логины на SQL сервер в другом домене, отредактируйте сгенерированный процедурой sp_help_revlogin скрипт и замените имя старого домена новым именем доменом во всех инструкциях sp_grantlogin. Поскольку логины в новом домене не будут иметь тот же самый SID как у логинов в старом домене, связь пользователей базы данных с логинами будет нарушена. Чтобы решать этих осиротевших пользователей, см. статьи, упомянутые ниже. Если Вы передаете NT логины между SQL серверами в одном домене, будет использоваться тот же самый SID, и пользователь не должны потерять связь со своими логинами.
2. После того, как логины будут перемещены, пользователи не будут иметь прежних разрешений по доступу к перемещённой базе данных. Эта проблема известна как "orphaned user". Если Вы попытаетесь предоставить логину доступ к базе данных, это может окончиться неудачей, с сообщением, что пользователя уже существует:
      Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.
   Для получения инструкций о том, как разрешать проблему таких "осиротевших" пользователей, изучите указанные ниже статьи Microsoft Knowledge Base. Для осиротевших SQL и NT логинов, см. статью:
      INF: How to Resolve Permission Issues When a Database is Moved (Q240872)
   Для получения информации о применении хранимой процедуры sp_change_users_login, которая перепривязывает к логинам осиротевших пользователей (это касается только пользователей, потерявших связь со стандартными SQL логинами), изучите следующую статью:
       PRB: Troubleshooting Orphaned Users Topic in BOL Incomplete (Q274188)
3. Такой подход становиться возможным из-за параметра @encryptopt в системной хранимой процедуре sp_addlogin, которая создаёт логин, используя зашифрованный пароль. Для получения дополнительной информации об этой процедуре, см. тему в SQL Server Books Online: "sp_addlogin (T-SQL)".
4. По умолчанию, только члены серверной роли sysadmin имеют право давать разрешение на выборку из таблицы sysxlogins. Если член роли sysadmin не предоставит необходимые разрешения, конечные пользователи не смогут создавать или выполнять эти хранимые процедуры.
5. Представленный выше подход не передаёт информацию о заданной по умолчанию базе данных для каждого логина, так как заданная по умолчанию база данных может быть иной на новом сервере. Чтобы установить заданную по умолчанию базу данных для логинов, используйте системную хранимую процедуру sp_defaultdb, указывая для неё в качестве параметров имя логина и заданную для него по умолчанию базу данных. Для получения подробной информации об использовании этой процедуры, см. в SQL Server Books Online тему: "sp_defaultdb".
6. В процессе передачи логинов между SQL серверами, если порядок сортировки исходного сервера - case-insensitive, а порядок сортировки нового сервера - case-sensitive, Вам придётся вводить на новом сервер все алфавитные символы в паролях в верхнем регистре.
7. Если порядок сортировки исходного сервера - case-sensitive, а порядок сортировки нового сервера - case-insensitive, Вы не сможете зарегистрироваться под перемещёнными логинами после использования процедуры, описанной в этой статье, если первоначальные пароли не содержали никаких алфавитных символов или если все алфавитные символы в первоначальных паролях были символами в верхнем регистре. Если оба сервера - case-sensitive или оба сервера - case-insensitive, у Вас не должно возникнуть таких проблем. Это побочный эффект механизма, с помощью которого SQL сервер обрабатывает пароли. Для получения дополнительной информации, см. тему в SQL Server 7.0 Books Online: "Effect on Passwords of Changing Sort Orders"
8. Если выполнять сгенерированный процедурой sp_help_revlogin скрипт на сервере, где уже заведены логин с такими же именами, как у логинов в этом скрипте, будет получена ошибка:
      Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
      The login 'test1' already exists.
   Аналогично, если на новом сервере уже существуют логин с тем же самым SID, Вы получите ошибку:
      Server: Msg 15433, Level 16, State 1, Procedure sp_addlogin, Line 93
      Supplied parameter @sid is in use.
   По этой причине, очень важно тщательно изучить содержимое сгенерированного скрипта и таблицы sysxlogins, что бы иметь возможность внести в его текст соответствующие изменения.
9. Значение SID для логина используется, как основание для доступа к базе данных SQL Server. Поэтому, если один логин имеет два разных SID (для двух разных база данных на одном сервере), этот логин будет иметь доступ только к той базе данных, чей SID соответствует значению в syslogins для этого логина. Такая ситуация может возникнуть, если эти две базы данных были объединены с двух разных серверов. Чтобы разрешить эту проблему, такой логин необходимо удалить из базы данных, что бы избавиться от несоответствия SID, используя хранимую процедуру sp_dropuser и добавить его снова, используя хранимую процедуру sp_adduser.

[Содержание]

Перевод: Александра Гладченко  2002г.