Идентификация имен входа при подключении к базе данных по умолчанию в SQL Server

В одной из своих предыдущих статьей я уже рассматривал «потерянные имена входа» (orphaned logins) — имена регистрации, для которых идентификатор безопасности (SID) не согласуется с соответствующей учетной записью пользователя базы данных. Читатели спрашивают меня, существует ли процесс, позволяющий определить еще один аспект сбоев подключения: имена входа, у которых назначенная база данных по умолчанию удалена после создания имени входа. В этой статье мы рассмотрим проблему идентификации таких имен входа, которая не позволяет отправлять запросы в базу данных, независимо от того, является ли она назначенной базой данных по умолчанию.

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

Моделирование ситуации отсутствующей базы данных по умолчанию

Может показаться, что вопрос достаточно простой: примените инструкцию SELECT к sys.server_principals для любого имени входа, когда база данных по умолчанию NULL. Однако в действительности он не так прост из-за отсутствия очистки — и ссылки по внешнему ключу — между sys.databases (системное представление для баз данных на экземпляре SQL Server) и sys.server_principals (системное представление для имени входа и ролей уровня сервера в Microsoft SQL Server).

Например, ситуация, описанная в приведенном выше вопросе пользователя: создать имя входа и назначить базу данных по умолчанию.
Добавить учетную запись пользователя к базе данных по умолчанию, чтобы имитировать условия успешного подключения к экземпляру SQL Server.
Удалить базу данных по умолчанию.

Рассмотрим код, приведенный в листинге 1.

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

Экран 1. База данных по умолчанию для имени входа

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

Различия в «болевой шкале» сообщения об ошибках

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

SELECT columnX
FROM database.schema.table;

Из какого приложения или источника пользователи выполняют подключения? В среде SQL Server Management Studio (в силу требований безопасности я не рекомендую ее в качестве источника подключения для конечных пользователей) сообщение об ошибке может ясно указывать пользователю путь к быстрому устранению корневой проблемы (экран 2).

Экран 2. Сообщение об ошибке в среде SQL Server Management Studio

В среде Visual Studio разработчику может встретиться менее понятная ошибка подключения, как на экране 3.

Экран 3. Ошибка подключения в среде Visual Studio

Разработчик, столкнувшийся с той же корневой ошибкой при подключении через Python, порой сталкивается с еще более сложной задачей обработки и идентификации, от которой может голова пойти кругом (экран 4).

Экран 4. Ошибка при подключении через Python

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

Сценарий для идентификации удаленных баз данных

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

Добавление LEFT JOIN к верному первоисточнику для баз данных на экземпляре SQL Server позволяет идентифицировать имена входа, которым была назначена база данных по умолчанию, уже не существующая на экземпляре (экран 5). Я надеюсь, что эта база данных не требуется вам для важных целей.

Экран 5. Имена входа, которым была назначена база данных по умолчанию

Исправление ситуации

Чтобы исправить положение, необходимо удалить имя входа или повторно назначить базу данных по умолчанию. Один вопрос пока остается открытым: динамическое удаление имени входа при возникновении подобных проблем. Мне хочется остановиться на этом вопросе, но в то же время не отражать его в приведенном образцовом сценарии, так как не все администраторы баз данных пожелают удалять имена входа. База данных может более не использоваться, но многим именам входа SQL Server на экземпляре нужен доступ к нескольким базам данных. Поэтому я предлагают два варианта:

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

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

Подготовить сценарий для динамического удаления имени входа просто (листинг 3). Как вы убедитесь, «просто» не означает, что этот путь правильный. Будьте осторожны, удаляя имена входа лишь по той причине, что ранее была удалена база данных по умолчанию. Имена входа могут быть связаны с пользовательскими объектами в других базах данных.

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

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

В случае многочисленных обращений администратор базы данных может выполнить команду ALTER LOGIN, что будет наиболее удачным решением для имени входа (экран 6). Это для меня еще одна причина считать, что хороший администратор базы данных не только знает внутреннее устройство платформы данных, но и понимает бизнес-правила и особенности эксплуатации базы данных.

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

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

Листинг 1. Создание имени входа

USE [master]
GO
-------------------------------------------
/*
Создание имени входа. Назначение базы данных по умолчанию.
Создание учетной записи пользователя в базе данных по умолчанию
*/
-------------------------------------------
CREATE LOGIN [foofoofoo]
WITH
        PASSWORD=N'foofoofoo'
        , DEFAULT_DATABASE=[foo]
        , CHECK_EXPIRATION=OFF
        , CHECK_POLICY=OFF
GO

USE [foo]
GO
CREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]
GO

-- CREATE TWO ADDITIONAL USERS FOR foofoofoo LOGIN FOR USE LATER IN ARTICLE:
USE [SQL_Cruise]
GO
CREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [iDBA]
GO
CREATE USER [foofoofoo] FOR LOGIN [foofoofoo] WITH DEFAULT_SCHEMA=[dbo]
GO
-------------------------------------------
/*
Идентификация имен входа с проблемами в базе данных по умолчанию
*/
-------------------------------------------
SELECT name
FROM sys.server_principals
WHERE default_database_name IS NULL
        AND type_desc != 'SERVER_ROLE'
ORDER BY name;
-------------------------------------------
/*
Удаление базы данных по умолчанию и повторная проверка
*/
-------------------------------------------
DROP DATABASE foo;

SELECT name
FROM sys.server_principals
WHERE default_database_name IS NULL
        AND type_desc != 'SERVER_ROLE'
ORDER BY name;

Листинг 2. Сценарий для идентификации удаленных баз данных по умолчанию

-------------------------------------------
/*
Удаление базы данных не удаляет назначение
базы данных по умолчанию. Необходимо определить, существует ли
еще база данных, с помощью простого объединения
*/
-------------------------------------------

SELECT SP.name
        , SP.default_database_name
FROM sys.server_principals AS SP
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE D.name IS NULL
        AND SP.type_desc != 'SERVER_ROLE'
ORDER BY SP.name;

Листинг 3. Динамическое удаление имени входа

--------------------------------------------
/*
Сценарий динамического удаления имени входа
*/
--------------------------------------------
SELECT SP.name
        , SP.default_database_name
        , 'DROP LOGIN [' + SP.name +'];'
FROM sys.server_principals AS SP
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE D.name IS NULL
        AND SP.type_desc != 'SERVER_ROLE'
ORDER BY SP.name;

Листинг 4. Динамическое исправление ситуации

---------------------------------------------------
/*
Динамическое назначение новой базы данных по умолчанию
для любого имени входа, у которого отсутствует база данных по умолчанию
*/
---------------------------------------------------
SET NOCOUNT ON;
DECLARE @sql_text nvarchar(max);
DECLARE @database_name sysname;

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

CREATE TABLE #existing_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
        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 #existing_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], ' + '''' + @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
        , DDUT.default_database_name AS potential_default_database_name
        , CASE
                WHEN DDUT.default_database_name IS NULL THEN 'Login has no other DB user associations.'
                ELSE 'ALTER LOGIN [' + SP.name + '] WITH DEFAULT_DATABASE=[' + DDUT.default_database_name + '];'
        END AS potential_default_database_command
FROM sys.server_principals AS SP
        LEFT JOIN #existing_db_users_table AS DDUT
                ON SP.name = DDUT.server_login_name
        LEFT JOIN sys.databases AS D
                ON SP.default_database_name = D.name
WHERE SP.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
        AND SP.name NOT LIKE 'NT %'
        AND SP.name NOT LIKE '##%'
        AND SP.default_database_name IS NOT NULL
        --AND DDUT.server_login_name IS NOT NULL
        AND D.name IS NULL
ORDER BY SP.name, DDUT.default_database_name;

--===================================================
-- Очистка
--===================================================
IF EXISTS(SELECT name FROM tempdb.sys.tables WHERE name LIKE '#missing_existing_db_users_table%')
        BEGIN
                DROP TABLE #missing_existing_db_users_table;
        END
GO