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

В этой статье рассматривается еще одна проблема, с которой мне недавно пришлось столкнуться у клиента. Компания перенесла свои экземпляры SQL Server с SQL Server 2012 на SQL Server 2016, однако не завершила перенос имен входа, что привело к неполадкам при подключениях, поскольку у некоторых имен входа отсутствует соответствующая учетная запись пользователя в базе данных по умолчанию. Давайте посмотрим, как такое может произойти и как идентифицировать и исправить ошибку.

Причины ситуации

Коренная причина описанной проблемы в существовании объекта имени входа в экземпляре SQL Server с назначенной базой данных по умолчанию, но без объекта пользователя, связанного с именем входа, существующим в базе данных по умолчанию. Это может произойти в результате различных событий.

1. Кто-то создал имя входа с базой данных по умолчанию, но впоследствии не создал учетную запись пользователя в базе данных, связанной с именем входа.

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

  • Отсутствует база данных по умолчанию, соответствующая назначенной для имени входа: назначенная база данных по умолчанию не перенесена. Возникает проблема, описанная в статье об идентификации имен входа, ссылка на которую приведена выше («Идентификация имен входа при подключении к базе данных по умолчанию в SQL Server»).
  • Копия базы данных с таким же именем, как у базы данных по умолчанию, была перенесена или создана с нуля.

3. Учетная запись пользователя была удалена из базы данных, но соответствующее имя входа не удалено.

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

Создание имени входа формирует сущность уровня экземпляра, которая связана с объектом Active Directory (Windows/Trusted Authentication) или с комбинацией имени входа и сохраненного пароля (SQL Server Authentication), как показано в листинге 1.

Процесс создания учетной записи пользователя формирует объект в области базы данных, связанный с именем входа на уровне экземпляра, на основе значения SID (уникальный идентификатор объекта безопасности в масштабе экземпляра в Microsoft SQL Server) (листинг 2).

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

Наконец, случай потерянной учетной записи пользователя: при удалении учетной записи пользователя из базы данных имя входа не удаляется. Это очень важно, поскольку можно иметь имя входа с правами пользователя в нескольких базах данных на экземпляре.

В ситуации, с которой мне пришлось столкнуться, имеются имена входа с базой данных по умолчанию, назначенной системной базой данных master. Так бывает часто, поскольку это гарантирует существование базы данных master на экземпляре Microsoft SQL Server. Никогда не возникнет ситуация, в которой вы удалите базу данных master. Однако вы не перенесете базу данных master при переносе экземпляров SQL Server на новые версии SQL Server. Пожалуй, единственный случай, когда происходит миграция базы данных master, — замена оборудования, и вы в плановом порядке переносите весь экземпляр на новое устройство. Или произошел общий отказ сервера, и тогда единственный выход — восстановить все базы данных из резервной копии на новом оборудовании. База данных master создается с нуля при установке SQL Server 2016, поэтому не переносится ни одна из учетных записей пользователей, существовавших в базе данных master экземпляра SQL 2012. В итоге каждое имя входа с master в качестве базы данных по умолчанию имеет отсутствующее звено в процессе проверки подлинности для имени входа. В моем случае идентифицировать и устранить проблему просто: выявить все имена входа с master в качестве базы данных по умолчанию и создать учетную запись пользователя для каждого имени входа в базе данных master.

Не такое простое решение

Ранее указывалось, что база данных master не всегда оказывается корневой причиной всех проблем с подключением. Консолидация — еще один хороший пример, как и переход между уровнями разработки, тестирования и производства. Поэтому приведенное ниже решение должно работать для любых баз данных на экземпляре SQL Server. Сначала я думал, что сценарий будет простым, поскольку логический подход к определению проблемы так же прост: проверить базу данных по умолчанию, назначенную каждому имени входа, и убедиться, что существует учетная запись пользователя с соответствующим значением SID для имени входа.

Это проще сказать, чем сделать, так как необходимо выполнить запрос в каждой базе данных для каждого имени входа и централизованно сохранить результаты для всех ситуаций, в которых отсутствует соответствующая учетная запись пользователя. Я остановился на решении, которое не одобряют многие авторитетные члены сообщества Microsoft Data Platform: курсоры. Однако при правильном подходе с помощью курсоров удается решить многие проблемы без ущерба для производительности. Поэтому я предпочел использовать курсор для обхода каждой базы данных и подготовки списка всех результатов для комбинации имени входа, учетной записи пользователя и базы данных по умолчанию, в зависимости от значения совпадений SID в двух системных представлениях, содержащих метаданные для имен входа сервера (master.sys.server_principals) и пользователей базы данных (sys._database_principals), существующих в каждой базе данных. Результаты сохраняются во временной таблице с использованием хранимой процедуры sys.sp_sqlexec, которая выполняет специализированный динамический код SQL, созданный с помощью курсора в контексте каждой базы данных. Наконец, эти результаты сравниваются с полным представлением master.sys.server_principals для любых отсутствующих пользователей и выдаются в качестве результатов запроса. Все сказанное демонстрирует код в листинге 3.

Последний столбец также содержит динамически сформированный код T-SQL, который может быть использован для создания объекта пользователя в назначенной базе данных по умолчанию (см. экран).

 

Результаты проверки и рекомендации
Экран. Результаты проверки и рекомендации

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

Листинг 1. Создание имени входа
CREATE LOGIN [timford]
WITH PASSWORD=N'thisissomepasswordisntit?'
        , DEFAULT_DATABASE=[SQL_Cruise]
        , CHECK_EXPIRATION=OFF
        , CHECK_POLICY=OFF;
Листинг 2. Процесс создания учетной записи
USE [SQL_Cruise]
GO
CREATE USER [timford] FOR LOGIN [timford];
Листинг 3. Проверка имен входа на связь с учетной записью пользователя
--======================================================
--Идентификация всех имен входа, с которыми не связан пользователь базы данных по умолчанию
--======================================================
SET NOCOUNT ON;

DECLARE @sql_text nvarchar(max);
DECLARE @database_name sysname;

IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#default_db_users_table%')
        BEGIN
                DROP TABLE #default_db_users_table;
        END

CREATE TABLE #default_db_users_table
        (
                server_login_name sysname NOT NULL
                , database_user_name sysname NOT NULL
                , default_database_name sysname NOT NULL
        );     

DECLARE cur_default_databases  FORWARD_ONLY STATIC FOR --FORWARD_ONLY STATIC  FOR
        SELECT name
        FROM sys.databases
        ORDER BY name

OPEN cur_default_databases

FETCH NEXT FROM cur_default_databases INTO @database_name

WHILE @@FETCH_STATUS = 0
        BEGIN

                SELECT @sql_text =
                'INSERT INTO #default_db_users_table(server_login_name, database_user_name, default_database_name)
                SELECT SP.name AS [server_login_name], DP.name AS [database_user_name], SP.default_database_name
                FROM sys.server_principals AS SP
                        INNER JOIN ' + @database_name + '.sys.database_principals AS DP
                                ON SP.sid = DP.sid
                WHERE SP.default_database_name = ''' + @database_name + ''';'

                EXEC sys.sp_sqlexec @sql_text;

                FETCH NEXT FROM cur_default_databases INTO @database_name
        END

CLOSE cur_default_databases;
DEALLOCATE cur_default_databases;

--===================================================
-- Список имен входа, не имеющих пользователя в базе данных по умолчанию
--===================================================
SELECT SP.name AS login_name
        , SP.default_database_name
        , 'USE [' + SP.default_database_name + ']; CREATE USER [' + SP.name + '] FOR LOGIN [' + SP.name + '];' AS user_create_stmt
FROM sys.server_principals AS SP
        LEFT JOIN #default_db_users_table AS DDUT
                ON SP.name = DDUT.server_login_name
WHERE SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
        AND SP.name NOT LIKE 'NT %'