Как перенести пользователей 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.

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

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

Создайте хранимые процедуры, которые помогут создать необходимые сценарии для передачи имен входа и паролей. Для этого подключитесь к серверу 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 для доступа из локальной сети
  Похожие публикации:

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

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

Введение

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

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

Замечания

Ссылки

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

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

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

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

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

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

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

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

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

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