none
проблема с переносом базы на другой сервер RRS feed

  • Вопрос

  • Имелась база данных на сервере под управлением MS SQL 2000 sp4, при переносе базы данных на другой сервер MS SQL 2000 sp4, в базе данных потерялась связь пользователя БД с логином sql (в моем случае это был windows nt user, локальный юзер той машины на котором была установлена БД). Каким образом можно восстановить утеряные соотношения? С помощью хранимой процедуры sp_change_users_login этого сделать не получается, и в документации касательно этой процедуры написано, что "sp_change_users_login can be used only with the security accounts of SQL server logins and users; it cannot be used with Microsoft Windows NT users."

    12 сентября 2007 г. 6:29

Ответы

  • Если 2000, то в QueryAnalyzer'e, 2005 - Management Studio. Насчёт allow updates - в 2k, если память не изменят, системный каталог доступен для изменений
    12 сентября 2007 г. 9:18
  •  

    http://www.dbforums.com/showthread.php?t=735308

    http://www.osp.ru/win2000/2003/03/175999/_p1.html

     

    Code Snippet

    Выдержка из статьи Кэлен Дилани:

    Sp_sidmap создает новые имена регистрации, аутентифицируемые сервером SQL Server, и устанавливает соответствие с любыми «потерянными» именами в восстановленной базе данных. Для установления соответствия с «потерянными» именами регистрации Windows процедура sp_sidmap дополнительно создает в восстановленной базе новые имена регистрации, аутентификация которых проходит в Windows. Sp_sidmap способна различить пользователей, проверка которых выполняется в SQL Server и в Windows, обращаясь к полю isntname в таблице Sysusers. Если значение isntname равно 1, запись такого пользователя относится к Login Name, проверяемому Windows; если значение равно 0, то запись в Sysusers относится к так называемой роли Database Role — к специальному пользователю, например guest, или к пользователю, проверяемому сервером SQL.

    Вторая хранимая процедура, sp_prefix_sysusersname, используется только тогда, когда Username базы данных может отождествляться с двумя возможными именами регистрации (аутентификация Windows) в двух разных доменах или на двух разных машинах. Когда администратор запускает sp_sidmap, процедура сообщает о наличии подобной ситуации и перечисляет Username и «кандидатов» из числа Login Name. После чего можно запустить _prefix_sysusersname и указать префикс во избежание неоднозначности подключения к SQL Server.

    При запуске sp_sidmap следует соблюдать осторожность и правильно указать все четыре параметра: имя домена, в котором находится исходная база данных, имя домена, в котором находится восстановленная база, имя сервера, на котором установлена исходная база, и имя сервера, на котором база данных восстановлена. Большая часть работы sidmap связана с поиском в таблице Sysusers исходного домена или имен компьютеров и заменой их новыми именами. Если пользователь на новом сервере в таблице Sysxlogins уже имеет соответствующее имя, процедура обновляет таблицу Sysusers правильным SID. Если для пользователя еще нет соответствующего Login Name, но сам пользователь существует в целевом домене или целевом сервере, процедура sp_sidmap создаст нового пользователя в Windows (аутентификация Windows). Например, если в восстанавливаемой базе данных есть пользователь OldDomain\Mary, а на новом сервере имеется Login Name вида NewDomain\Mary, хранимая процедура sp_sidmap обновит запись в Sysusers для OldDomain\Mary на NewDomain\Mary и SID в Sysusers для установления соответствия SID в записи Sysxlogins. Если же NewDomain\Mary в Sysxlogins отсутствует, но в NewDomain имеется пользователь операционной системы Mary, процедура sp_sidmap создаст нового пользователя NewDomain\Mary и внесет те же самые исправления в таблицу Sysusers. Проблема возникает только в том случае, если в NewDomain нет подходящего пользователя (Username). Тогда sp_sidmap выдает сообщение об ошибке.

    В хранимой процедуре sp_sidmap есть недокументированная особенность: коль скоро в таблице Sysxlogins находится подходящее имя регистрации, процедура не спрашивает — относится ли оно к домену или прописано на локальной станции. Если администратор домена до этого удалял имя NewDomain\Mary, имея в виду пользователя домена, то же самое имя не будет автоматически удаляться из таблицы Sysxlogins на сервере SQL. А поскольку это имя существует в Sysxlogins, процедура sp_sidmap станет использовать это имя и соответствующий SID (ставший теперь бессмысленным) для обновления восстановленной таблицы Sysusers. Если SQL Server сконфигурирован для работы только в режиме Windows-Authentication Mode, «оригинальная» учетная запись Mary из старого домена уже не может быть зарегистрирована в восстановленной базе данных, а sp_sidmap ничего не сообщит о том, что Mary больше не имеет доступа к базе.

    В статье «INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers» специалисты Microsoft строго рекомендуют перед запуском sp_sidmap убедиться, что все учетные записи пользователей в базе данных имеются и в домене или на локальном сервере, и всем им обеспечен доступ к SQL Server, т. е. для них есть свои записи в таблице Sysxlogins. С одной стороны, такая рекомендация не вызывает возражений. Однако если у вас на самом деле должны быть пользователи (Username), для которых не нужно устанавливать соответствия с Login Name, хранимая процедура sp_sidmap в одиночку с подобной ситуацией не справится. Например, если на исходном сервере есть OldDomain\Charlie (Login Name), которому соответствует carlos (Username), возможно два решения. Согласно установке Microsoft, администратор базы данных (DBA) должен вручную обновить таблицу Sysusers после запуска sp_sidmap; однако применять такое решение я бы не советовала до тех пор, пока администратора устраивает обновленная системная таблица. Microsoft рекомендует до запуска sp_sidmap перенести во временную базу все объекты, владельцем которых является Charlie удалить учетную запись пользователя carlos, добавить ее для пользователя Charlie и затем перенести обратно все объекты, ранее принадлежавшие пользователю carlos.

    Такое решение кажется несколько сложным, особенно с учетом того, что существует хранимая процедура sp_changeobjectowner. Вместо того чтобы использовать описанное выше решение, DBA может просто добавить нового пользователя Charlie в базу данных и воспользоваться sp_changeobjectowner для изменения владельца всех объектов carlos на Charlie. Затем учетная запись для carlos удаляется — до того момента, как начнется резервное копирование базы. Конечно, ни решение Microsoft, ни мой подход не рассматривает ситуацию, когда требуется оставить пользователя carlos в базе, хотя имя регистрации — Charlie. Если такая путаница в именах никому не мешает, нужно без промедления обновить системные таблицы после завершения работы процедуры sp_sidmap. Тогда все прежние пользователи смогут подключиться к базе данных под своими именами регистрации.

     

    17 сентября 2007 г. 18:46

Все ответы

  • Пробовали?
    http://www.sql.ru/forum/actualtopics.aspx?search=%EF%E5%F0%E5%ED%EE%F1+%EB%EE%E3%E8%ED%EE%E2&submit=%CD%E0%E9%F2%E8&bid=1
    12 сентября 2007 г. 7:17
  • да вот накопал там инфы, но что это значит понять не могу т.к. с sql вообще практически не работал:

     

    "На 2000 подмена СИДов на ура работает, только аллоу апдейтс надо поставить:

     EXEC('update '+@DatabaseName+'..sysusers SET
    sid=(SELECT sid from master..syslogins l where l.name=CAST(serverproperty('
    'MachineName'') as varchar(40))+'''+@LoginName+'''),
    name=(SELECT name from master..syslogins l where l.name=CAST(serverproperty('
    'MachineName'') as varchar(40))+'''+@LoginName+''')
    where name LIKE '
    ''+@UserName+'''')"

     

    где это надо выполнять и каков будет результат выполнения? что такое allow updates?

    12 сентября 2007 г. 7:59
  • Если 2000, то в QueryAnalyzer'e, 2005 - Management Studio. Насчёт allow updates - в 2k, если память не изменят, системный каталог доступен для изменений
    12 сентября 2007 г. 9:18
  • ясно ясно, с похожей проблемой насколько я понимаю Вы не сталкивались.

    12 сентября 2007 г. 14:45
  • У нас ещё более сложная проблема. Сервера в разных контроллерах домена, сервер источник MS SQL 2005 сервер приемник MS SQL 2005 SP2. Сервера в разных доменах, причем на сервере-источнике в качестве юзеров MS SQL берутся пользователи домена. В старом домене, по причине экспериментов потеряна часть функциональности у  контроллера домена утеряна, ввести оба компьютера в один домен не получается. Стандартными(рекомендуемыми) средствами скопировать/переместить базу из одного сервера в другой не получается. А в базе данных хранится работа целого коллектива за 6 месяцев. Резонный вопрос: Что делать?
       Я понимаю что связи пользователей будут утеряны, это допустимо, пользователей можно завести заново, но вот как перенести данные. Помогите пожалуйста.
    13 сентября 2007 г. 8:56
  • Восстановите данные из резервной копии.

    Проблема "осиротевших" пользователей давно известна и хорошо документирована, что конкретно не получается?

    13 сентября 2007 г. 12:04
  •  

    Александр, если Вы имеет ввиду вот эту статью http://support.microsoft.com/kb/274188/ то эта проблема действительно легко решается, но только если логины SQL это не windows nt логины. В нашем случае это не помогает.
    14 сентября 2007 г. 8:55
  •  Илья М. - MCP написано:

     

    Александр, если Вы имеет ввиду вот эту статью http://support.microsoft.com/kb/274188/ то эта проблема действительно легко решается, но только если логины SQL это не windows nt логины. В нашем случае это не помогает.

     

    Нет, я имел ввиду это: http://msdn2.microsoft.com/ru-ru/library/11eefa97-a31f-4359-ba5b-e92328224133.aspx

    и это: http://support.microsoft.com/kb/240872/ru-ru/

    Напишите что не получается, подробно и по шагам...?

    14 сентября 2007 г. 18:32
  • http://msdn2.microsoft.com/ru-ru/library/11eefa97-a31f-4359-ba5b-e92328224133.aspx

    вот эта статья не подходит, т.к. на старом сервере использовались windows NT логины для связки с юзером БД

     

    касательно второй статьи, по шагам:

    1) нашел в сети sp_sidmap , т.к. ссылка на MapSids.exe на microsoft битая, следовательно отсутствует необходимый файл readme.txt

    2) сделал все необходимые процедуры по созданию хранимых процедур sp_sidmap и sp_prefix_sysusersname.

    3) выполнил

    EXEC sp_SidMap @old_domain = old_domain_name,
    @new_domain = new_domain_name,
    @old_server = old_server_name,
    @new_server = new_server_name

     

    но стоит отметить что обе машины у меня были не в домене, а имена обоих машины идентичны, в итоге делал так:

     

    EXEC sp_SidMap @old_domain = srv-site,
    @new_domain = srv-site,
    @old_server = srv-site,
    @new_server = srv-site

     

    4) а вот дальше в той статье идёт пункт

    "Сохраните полученный результат в файле и следуйте указаниям, которые предоставлены в файле Readme.txt."

    так как файла readme.txt нет, то продолжить решение проблемы не представляется возможным.

    вот ссылка, не рабочая - http://download.microsoft.com/download/sqlsvr2000/utility/5.0/win98me/en-us/mapsids.exe, а где взять mapsids.exe - непонятно.

    что посоветуете делать?

    17 сентября 2007 г. 9:44
  •  

    http://www.dbforums.com/showthread.php?t=735308

    http://www.osp.ru/win2000/2003/03/175999/_p1.html

     

    Code Snippet

    Выдержка из статьи Кэлен Дилани:

    Sp_sidmap создает новые имена регистрации, аутентифицируемые сервером SQL Server, и устанавливает соответствие с любыми «потерянными» именами в восстановленной базе данных. Для установления соответствия с «потерянными» именами регистрации Windows процедура sp_sidmap дополнительно создает в восстановленной базе новые имена регистрации, аутентификация которых проходит в Windows. Sp_sidmap способна различить пользователей, проверка которых выполняется в SQL Server и в Windows, обращаясь к полю isntname в таблице Sysusers. Если значение isntname равно 1, запись такого пользователя относится к Login Name, проверяемому Windows; если значение равно 0, то запись в Sysusers относится к так называемой роли Database Role — к специальному пользователю, например guest, или к пользователю, проверяемому сервером SQL.

    Вторая хранимая процедура, sp_prefix_sysusersname, используется только тогда, когда Username базы данных может отождествляться с двумя возможными именами регистрации (аутентификация Windows) в двух разных доменах или на двух разных машинах. Когда администратор запускает sp_sidmap, процедура сообщает о наличии подобной ситуации и перечисляет Username и «кандидатов» из числа Login Name. После чего можно запустить _prefix_sysusersname и указать префикс во избежание неоднозначности подключения к SQL Server.

    При запуске sp_sidmap следует соблюдать осторожность и правильно указать все четыре параметра: имя домена, в котором находится исходная база данных, имя домена, в котором находится восстановленная база, имя сервера, на котором установлена исходная база, и имя сервера, на котором база данных восстановлена. Большая часть работы sidmap связана с поиском в таблице Sysusers исходного домена или имен компьютеров и заменой их новыми именами. Если пользователь на новом сервере в таблице Sysxlogins уже имеет соответствующее имя, процедура обновляет таблицу Sysusers правильным SID. Если для пользователя еще нет соответствующего Login Name, но сам пользователь существует в целевом домене или целевом сервере, процедура sp_sidmap создаст нового пользователя в Windows (аутентификация Windows). Например, если в восстанавливаемой базе данных есть пользователь OldDomain\Mary, а на новом сервере имеется Login Name вида NewDomain\Mary, хранимая процедура sp_sidmap обновит запись в Sysusers для OldDomain\Mary на NewDomain\Mary и SID в Sysusers для установления соответствия SID в записи Sysxlogins. Если же NewDomain\Mary в Sysxlogins отсутствует, но в NewDomain имеется пользователь операционной системы Mary, процедура sp_sidmap создаст нового пользователя NewDomain\Mary и внесет те же самые исправления в таблицу Sysusers. Проблема возникает только в том случае, если в NewDomain нет подходящего пользователя (Username). Тогда sp_sidmap выдает сообщение об ошибке.

    В хранимой процедуре sp_sidmap есть недокументированная особенность: коль скоро в таблице Sysxlogins находится подходящее имя регистрации, процедура не спрашивает — относится ли оно к домену или прописано на локальной станции. Если администратор домена до этого удалял имя NewDomain\Mary, имея в виду пользователя домена, то же самое имя не будет автоматически удаляться из таблицы Sysxlogins на сервере SQL. А поскольку это имя существует в Sysxlogins, процедура sp_sidmap станет использовать это имя и соответствующий SID (ставший теперь бессмысленным) для обновления восстановленной таблицы Sysusers. Если SQL Server сконфигурирован для работы только в режиме Windows-Authentication Mode, «оригинальная» учетная запись Mary из старого домена уже не может быть зарегистрирована в восстановленной базе данных, а sp_sidmap ничего не сообщит о том, что Mary больше не имеет доступа к базе.

    В статье «INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers» специалисты Microsoft строго рекомендуют перед запуском sp_sidmap убедиться, что все учетные записи пользователей в базе данных имеются и в домене или на локальном сервере, и всем им обеспечен доступ к SQL Server, т. е. для них есть свои записи в таблице Sysxlogins. С одной стороны, такая рекомендация не вызывает возражений. Однако если у вас на самом деле должны быть пользователи (Username), для которых не нужно устанавливать соответствия с Login Name, хранимая процедура sp_sidmap в одиночку с подобной ситуацией не справится. Например, если на исходном сервере есть OldDomain\Charlie (Login Name), которому соответствует carlos (Username), возможно два решения. Согласно установке Microsoft, администратор базы данных (DBA) должен вручную обновить таблицу Sysusers после запуска sp_sidmap; однако применять такое решение я бы не советовала до тех пор, пока администратора устраивает обновленная системная таблица. Microsoft рекомендует до запуска sp_sidmap перенести во временную базу все объекты, владельцем которых является Charlie удалить учетную запись пользователя carlos, добавить ее для пользователя Charlie и затем перенести обратно все объекты, ранее принадлежавшие пользователю carlos.

    Такое решение кажется несколько сложным, особенно с учетом того, что существует хранимая процедура sp_changeobjectowner. Вместо того чтобы использовать описанное выше решение, DBA может просто добавить нового пользователя Charlie в базу данных и воспользоваться sp_changeobjectowner для изменения владельца всех объектов carlos на Charlie. Затем учетная запись для carlos удаляется — до того момента, как начнется резервное копирование базы. Конечно, ни решение Microsoft, ни мой подход не рассматривает ситуацию, когда требуется оставить пользователя carlos в базе, хотя имя регистрации — Charlie. Если такая путаница в именах никому не мешает, нужно без промедления обновить системные таблицы после завершения работы процедуры sp_sidmap. Тогда все прежние пользователи смогут подключиться к базе данных под своими именами регистрации.

     

    17 сентября 2007 г. 18:46