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

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

Хранимая процедура системы sp_msForEachDB – это удобный инструмент, который может выполнять пакет операторов для каждой базы данных на сервере. Используя эту процедуру, мы можем автоматизировать процесс проверки и синхронизации логинов во всех базах данных.

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

DECLARE @Collation varchar(100)
DECLARE @SQL VARCHAR(2000)

CREATE TABLE ##TempSync (DB_NME Varchar(50), DBUserName varchar(50), SysLoginName varchar(50))

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
SELECT ''?'' DB_NME, A.name DBUserName, B.loginname SysLoginName 
FROM sysusers A 
JOIN master.dbo.syslogins B ON A.name Collate ' + @Collation + ' = B.Name 
JOIN master.dbo.sysdatabases C ON C.Name = ''?'' 
WHERE issqluser = 1 
AND (A.sid IS NOT NULL AND A.sid <> 0x0) 
AND suser_sname(A.sid) IS NULL 
AND (C.status & 32) = 0 --loading 
AND (C.status & 64) = 0 --pre recovery 
AND (C.status & 128) = 0 --recovering 
AND (C.status & 256) = 0 --not recovered 
AND (C.status & 512) = 0 --offline 
AND (C.status & 1024) = 0 --read only 
ORDER BY A.name'

INSERT into ##TempSync EXEC sp_msforeachdb @SQL

SELECT * FROM ##TempSync

DROP TABLE ##TempSync

Этот скрипт сравнивает пользователей в каждой базе данных с пользователями в базе данных master и проверяет, совпадают ли их SID. Если совпадения нет, скрипт может быть изменен для выполнения необходимой хранимой процедуры для синхронизации логина для этого конкретного пользователя.

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

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

DECLARE @Collation VARCHAR (100)
DECLARE @SQL VARCHAR(2000)

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)

DECLARE SyncDBLogins CURSOR FOR 
SELECT A.name DBUserName, B.loginname SysLoginName 
FROM sysusers A 
JOIN master.dbo.syslogins B ON A.name Collate ' + @Collation + ' = B.Name 
JOIN master.dbo.sysdatabases C ON C.Name = ''?'' 
WHERE issqluser = 1 
AND (A.sid IS NOT NULL AND A.sid <> 0x0) 
AND suser_sname(A.sid) IS NULL 
AND (C.status & 32) = 0 --Loading 
AND (C.status & 64) = 0 --pre recovery 
AND (C.status & 128) = 0 --recovering 
AND (C.status & 256) = 0 --not recovered 
AND (C.status & 512) = 0 --offline 
AND (C.status & 1024) = 0 --read only 
ORDER BY A.name

OPEN SyncDBLogins

FETCH NEXT FROM SyncDBLogins INTO @DBUserName, @SysLoginName

WHILE @@FETCH_STATUS = 0 
BEGIN 
    EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName 
    FETCH NEXT FROM SyncDBLogins INTO @DBUserName, @SysLoginName 
END

CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins '

EXEC sp_msforeachdb @SQL

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

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

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

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