|
|
По материалам статьи Microsoft: INF: How To Transfer Logins and Passwords Between SQL Servers (Q246133)
Введение Информация в этой статье относится к Microsoft SQL Server 7.0/2000 (все издания) После перемещения базы данных в другой сервер, пользователи
прежнего SQL сервера не смогут подключиться к новому серверу.
Это происходит потому, что на новом сервере отсутствуют логины
этих пользователей и их необходимо восполнить. В статье
Microsoft Q246133 предлагается решение, которое позволяет
упростить процедуру передачи логинов на другой сервер и
описываются наиболее типичные проблемы, которые при этом могут
возникнуть. Msg 18456, Level 16, State
1 Вы должны передать логины и пароли на новый сервер. Передача логинов и паролей между серверами 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, можно использовать новый DTS Package Transfer Logins Task, входящий в комплект SQL Server 2000. Для этого нужно: 1. Подключитесь к SQL Server 2000, на который необходимо
перенести логины с паролями, и используйте Data
Transformation Services входящий в поставку SQL Server
Enterprise Manager, разверните папку с таким же именем в
дереве нового сервера и щёлкните правой кнопкой мыши по
Local Packages, а затем выберете New
Package. ОБРАТИТЕ ВНИМАНИЕ: Вы можете использовать метод с 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, и пользователь
не должны потерять связь со своими логинами. 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)". Server: Msg 15025, Level 16, State 1,
Procedure sp_addlogin, Line 56 Аналогично, если на новом сервере уже существуют логин с тем же самым SID, Вы получите ошибку: Server: Msg 15433, Level 16, State 1,
Procedure sp_addlogin, Line 93 По этой причине, очень важно тщательно изучить содержимое
сгенерированного скрипта и таблицы sysxlogins, что бы иметь
возможность внести в его текст соответствующие
изменения. |
Перевод: Александр Гладченко 2002г. |