Перенос логинов с паролями Microsoft SQL Server 2005.

Надумал поменять сервер, отработавший 5 лет, на новую железку. Как обычно вылезла куча проблем, о существовании которых за время, прошедшее с момента предыдущей установки, было благополучно забыто. Одна из них - как перетащить логины юзеров SQL 2005 на новый сервер.



Как перенести базу
Останавливаем исходный сервер и копируем файлы нужной БД на новый сервер.
На новом сервере запускаем SQL Server Management Studio. Переходим к пункту Databases, щелкаем по нему правой кнопкой мыши и выбираем в контекстном меню пункт Attach... Дальше описывать нет смысла, поскольку все предельно ясно.

Перенос логинов
Методом гугленья был найден следующий метод:
На ИСХОДНОМ сервере необходимо выполнить скрипт -

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 '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 ''
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. Затем на том же сервере выполняем -

EXEC master..sp_help_revlogin

Результатом выполнения этой хранимой процедуры является скрипт, который запускаем на НОВОМ сервере. Если имена нового домена и нового сервера отличаются от исходных, то  необходимо произвести их замену в полученном скрипте. Например открыть скрипт с помощью блокнота нажать Ctrl+H и произвести замену.
Важные примечания от Microsoft:

Перед выполнением выходного сценария в экземпляре на сервере 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.

Честно стырено отсюда. Спасибо пользователю Begemot
Первоисточник тут.