Как перенести пользователей sql на другой сервер
Перейти к содержимому

Как перенести пользователей sql на другой сервер

  • автор:

Передача имен входа и паролей между экземплярами SQL Server

В этой статье описывается, как передавать имена входа и пароли между различными экземплярами SQL Server под управлением Windows.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 918992, 246133

Введение

В этой статье описывается способ передачи имен входа и паролей между различными экземплярами Microsoft SQL Server.

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

Дополнительные сведения

В этой статье сервер A и сервер B являются разными серверами.

После перемещения базы данных с экземпляра SQL Server на сервере A в экземпляр SQL Server на сервере B пользователи могут не входить в базу данных на сервере B. Кроме того, пользователи могут получить следующее сообщение об ошибке:

Сбой входа для пользователя «MyUser«. (Microsoft SQL Server, ошибка: 18456)

Эта проблема возникает из-за того, что вы не передали имена входа и пароли из экземпляра SQL Server на сервере A в экземпляр SQL Server на сервере B.

Сообщение об ошибке 18456 также возникает по другим причинам. Дополнительные сведения об этих причинах и возможных решениях см. в разделе MSSQLSERVER_18456.

Чтобы передать имена входа, воспользуйтесь одним из описанных ниже способов в зависимости от ситуации.

    Способ 1. Сброс пароля на целевом SQL Server компьютере (сервер B). Чтобы устранить эту проблему, сбросьте пароль на компьютере SQL Server, а затем выполните сценарий входа.

Примечание. Алгоритм хэширования паролей используется при сбросе пароля.

    Создайте хранимые процедуры, которые помогут создать необходимые сценарии для передачи имен входа и паролей. Для этого подключитесь к серверу A с помощью SQL Server Management Studio (SSMS) или любого другого клиентского средства и запустите следующий сценарий: 
 USE [master] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE [dbo].[sp_hexadecimal] ( @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT ) AS BEGIN 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 'sa' AND p.name not like '##%' ORDER BY p.name END ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name 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 ORDER BY p.name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 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 SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''') BEGIN' Print @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 SET @Prefix = ' EXEC master.dbo.sp_addsrvrolemember @loginame=''' SET @tmpstrRole='' SELECT @tmpstrRole = @tmpstrRole + CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END + CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END + CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END + CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END + CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END + CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END + CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END + CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END FROM ( SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName], sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin FROM sys.syslogins WHERE ( sysadmin<>0 OR securityadmin<>0 OR serveradmin<>0 OR setupadmin <>0 OR processadmin <>0 OR diskadmin<>0 OR dbcreator<>0 OR bulkadmin<>0 ) AND name=@name ) L PRINT @tmpstr PRINT @tmpstrRole PRINT 'END' END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage END CLOSE login_curs DEALLOCATE login_curs RETURN 0 END

Примечание. Этот сценарий создает две хранимые процедуры в главной базе данных. Процедуры называются sp_hexadecimal и sp_help_revlogin.

EXEC sp_help_revlogin

Ознакомьтесь со сведениями в следующем разделе Примечания, прежде чем приступить к реализации действий на целевом сервере.

Действия на целевом сервере (сервер B)

Подключитесь к серверу B с помощью любого клиентского средства (например, SSMS), а затем запустите скрипт, созданный на шаге 4 (выходные sp_helprevlogin данные ) на сервере A.

Замечания

Перед запуском сценария вывода на экземпляре на сервере B просмотрите следующие сведения:

  • Хэширование пароля может выполняться следующими способами:
    • VERSION_SHA1 : этот хэш создается с помощью алгоритма SHA1 и используется в SQL Server, начиная с версии от 2000 до 2008 R2.
    • VERSION_SHA2 : этот хэш создается с помощью алгоритма SHA2 512 и используется в SQL Server 2012 и более поздних версиях.
    • Сервер A без учета регистра и сервер B с учетом регистра. Порядок сортировки сервера A может быть без учета регистра, а порядок сортировки сервера B может быть с учетом регистра. В этом случае пользователи должны ввести пароли заглавными буквами после передачи имен входа и паролей экземпляру на сервере B.
    • Сервер A с учетом регистра и сервер B без учета регистра: Порядок сортировки сервера A может учитывать регистр, а порядок сортировки сервера B — без учета регистра. В этом случае пользователи не могут войти в систему с помощью имен входа и паролей, передаваемых экземпляру на сервере B, если не выполняется одно из следующих условий:
      • Исходные пароли не содержат букв.
      • Исходные пароли содержат только прописные буквы.

      Сообщение 15025, уровень 16, состояние 1, строка 1
      Субъект-сервер «MyLogin» уже существует.

      Аналогичным образом имя входа, которое уже находится в экземпляре на сервере B, может иметь идентификатор безопасности, аналогичный идентификатору безопасности в выходном скрипте. В этом случае при запуске сценария вывода на экземпляре на сервере B отобразится следующее сообщение об ошибке:

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

      1. Внимательно просмотрите сценарий вывода.
      2. Изучите содержимое sys.server_principals представления в экземпляре на сервере B.
      3. Устраните эти сообщения об ошибках соответствующим образом. В SQL Server 2005 для реализации доступа на уровне базы данных используется идентификатор безопасности для имени входа. Имя входа может иметь разные идентификаторы БЕЗОПАСНОСТИ в разных базах данных на сервере. В этом случае имя входа может получить доступ только к базе данных с идентификатором безопасности, который соответствует идентификатору безопасности в представлении sys.server_principals . Эта проблема может возникнуть, если две базы данных объединены с разных серверов. Чтобы устранить эту проблему, вручную удалите имя входа из базы данных с несоответствием идентификатора безопасности с помощью инструкции DROP USER. Затем снова добавьте имя входа с помощью инструкции CREATE USER .

      Ссылки

      • Устранение проблемы с потерянными пользователями
      • CREATE LOGIN (Transact-SQL)
      • ALTER LOGIN (Transact-SQL)

      Обратная связь

      Были ли сведения на этой странице полезными?

      Как перенести базу данных с одного сервера на другой

      Перенос базы данных MySQL можно разделить на 4 этапа:

      1. Создание дампа базы.
      2. Перенос дампа на новый сервер.
      3. Создание “пустой” БД на новом сервере и восстановление дампа в неё.
      4. Настройка прав доступа к БД.

      Перед началом работы с MySQL убедитесь, что на сервере запущен демон mysql.

      Как сделать дамп базы данных

      Для создания дампа БД можно воспользоваться следующей командой:

      mysqldump -u root -p -f myolddb > /home/username/mydbdump.sql

      Затем вводим пароль пользователя:

      mypassword

      Рассмотрим первую команду. Для создания дампа, мы:

      • воспользовались утилитой mysqldump от имени пользователя MySQL root (ключ –u) (не путать с суперпользователем сервера root);
      • задали проверку пароля (ключ –p);
      • “попросили” создавать дамп даже при возникновении ошибок MySQL (ключ –f);
      • указали имя БД (myolddb);
      • указали директорию, в которой должен быть сохранён дамп БД (/home/username/);
      • указали имя самого дампа (mydbdump.sql).

      Перенос базы данных на новый сервер

      Следующим шагом является перенос дампа на новый сервер. Для этого можно воспользоваться ftp-клиентом, подключиться к старому серверу, скачать дамп на домашний компьютер, и, подключившись к новому серверу, загрузить дамп на него. Другим способом переноса дампа, для которого не нужно выполнять промежуточное копирование на домашнем компьютере, является использование команды wget на новом сервере, с указанием ссылки на старый сервер (например, http://oldserver.com/mydbdump.sql). Однако для использования данной команды необходимо, чтобы на старом сервере был запущен веб-сервер, а файл дампа помещён в корневую директорию хоста oldserver.com (например, /var/www/html).

      После того как дамп перенесён, его нужно восстановить на новом сервере. Для начала необходимо войти в MySQL и создать “пустую” БД.

      mysql –u root –p mypassword CREATE DATABASE mynewdb; quit

      Восстановление дампа базы данных

      Далее восстанавливаем дамп в только что созданную БД.

      mysql -u root -p -f mynewdb < /home/username/mydbdump.sql mypassword

      Настройка прав доступа к БД

      Наконец, необходимо настроить права доступа к БД, а именно определить, какой пользователь будет иметь доступ к данной БД. Предположим, Вы устанавливаете WordPress и хотите, чтобы доступ к БД имел пользователь под именем wordpress. В таком случае, нужно войти в MySQL как root при помощи команды:

      mysql –u root –p

      и выполнить следующие команды:

      GRANT ALL ON mynewdb.* to wordpress@localhost identified by 'wordpresspassword'; FLUSH PRIVILEGES; quit

      Данная команда не только настраивает права доступа к БД, но также создаёт пользователя БД (например, wordpress) и устанавливает для него пароль (wordpresspassword).

      Вы можете проверить корректность создания пользователя:

      mysql –u wordpress –p wordpresspassword SHOW DATABASES;

      При успешной настройке прав доступа Вы увидите следующий текст:

      Как перенести логины и пароли в MS SQL Server

      При переносе баз с одного экземпляра SQL Server на другой имена входа (логины) и пароли к ним не переносятся автоматически. Соответственно после переноса пользователи не смогут подключиться к своей базе данных и получат сообщение об ошибке.

      Избежать подобной проблемы можно следующим образом. Сначала на исходном сервере, с которого перенесены базы, выполняем следующий запрос:

      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 'Имена не найдены.' 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

      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 '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 'Имена не найдены.' 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. Запускаем процедуру sp_help_revlogin:

      EXEC sp_help_revlogin

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

      При переносе логинов надо учитывать некоторые тонкости:

      • Для выполнения данной операции требуется иметь разрешение на выполнение SELECT из представления sys.server_principals. По умолчанию эти разрешения имеют только члены sysadmin;
      • Перед выполнением выходного запроса необходимо проверить наличие совпадающих логинов на исходном и целевом серверах. Если логин, имеющийся на целевом сервере, совпадает с логином в выходном скрипте, то при выполнении скрипта будет выдана ошибка;
      • Надо учитывать параметры сортировки на исходном и целевом серверах;
      • Если исходный и целевой серверы находятся в разных доменах, то перед выполнением выходной запрос необходимо просмотреть и отредактировать, заменив в операторах CREATE LOGIN исходное имя домена на новое. При этом могут возникнуть проблемы с разрешениями.

      Более подробно о переносе логинов можно почитать на сайте Microsoft. Статья относится к SQL Server 2005, однако указанный способ актуален и для версий SQL Server 2012\2014. На 2016 не проверял, но по идее также должно работать.

      Как перенести пользователей Microsoft SQL на новый сервер

      Предыстория: понадобилось мне перенести очень большое количество БД с одного сервера на другой в связи… (да не важно в связи с чем, просто была нужда). Но так как Баз Данных было очень много, а разрешений для этих БД было еще больше, да и логины/пароли забивать для этих учетных записей хотелось и того меньше, пришлось искать обходные пути, как упростить данное мероприятие. И оно нашлось! Да, проверено данное действо на Microsoft SQL Server 2012 и 2014. Предположительно будет работать и в более высоких версиях включая 2019, но это не точно.

      Итак, что у нас есть:
      1. Приаттаченные БД на новом сервере;
      2. В наличии пока еще живой старый сервер SQL c с которого эти БД переносились.

      Предварительно мы имеем подобные симптомы:
      При переносе баз с одного экземпляра SQL Server на другой имена входа (логины) и пароли к ним не переносятся автоматически. Соответственно после переноса пользователи не смогут подключиться к своей базе данных и получат сообщение об ошибке.

      Инструкция к действию:
      1. На исходном сервере, с которого перенесены базы, выполняем следующий запрос на БД Master:

      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 'Имена не найдены.'
      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

      2.Этот запрос создаст в базе master две хранимых процедуры — sp_hexadecimal и sp_help_revlogin.
      Запускаем процедуру sp_help_revlogin:

      USE master
      GO
      EXEC sp_help_revlogin

      3. В результате мы должны получить список созданных ранее пользователей, с хэшами паролей и настройками доступа к приаттаченным ранее БД.
      4. Копируем данный скрипт на новый сервер, выбирая тех пользователей которые нам необходимы и выполняем этот скрипт на базе master.

      Да, еще есть не тру-метод, это найти вот такую программу, которая из master.mdf сама выдерет пароли/логины, но само собой, о правах на БД речи не идет.
      И да, это не реклама. В своем уме платить за нее 50$ никакого желания нет.
      SQL Server Password Changer

      Похожие записи:

      1. Enable and Configure FILESTREAM for SQL Server
      2. Shrink базы данных MS SQL
      3. Настройка MS SQL Express для доступа из локальной сети
      Похожие публикации:
      1. Как вызвать метод из класса java
      2. Как пользоваться стиральной машиной indesit wiun 81
      3. Сколько масла в двигателе lada granta
      4. Сколько синхронизаторов на кпп уаз пятиступка

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *