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

Определение потерянных учетных данных

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

После того как потерянные учетные данные идентифицированы, у администратора есть два варианта: удалить учетные данные, если они более не нужны в данном экземпляре, или назначить существующую базу данных базой данных по умолчанию. В приведенном в листинге 1 примере функции реализованы с базой данных master; ваш выбор может быть иным. Вы можете повторять и настраивать выбор, заменив код WITH DEFAULT_DATABASE=[master] параметром Template следующим образом: WITH DEFAULT_DATABASE= [].

Результаты, полученные в моей тестовой среде, показаны на экране; существуют два имени входа, для которых удалена база данных по умолчанию. В третьем столбце формируется код T-SQL, который заменяет базу данных по умолчанию на master и создает пользователя базы данных с членством только в общедоступной роли.

 

Результаты работы кода в листинге 1
Экран. Результаты работы кода в листинге 1

Обратимся к третьему столбцу. Сетка результатов плохо отражает сущность значений (листинг 2). Вы видите, что значения предоставляют код T-SQL для переназначения учетных данных базе данных по умолчанию, которая никогда не удаляется: master. Конечно, это лишь в случае, если необходимо сохранить учетные данные, которым могут быть предоставлены права в нескольких базах данных, а не только в базе данных, удаленной на каком-то этапе.

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

Листинг 1. Обработка потерянных учетных данных
SELECT SL.name, SL.dbname
, 'USE [master];
ALTER LOGIN [' + SL.name + '] WITH DEFAULT_DATABASE=[master];
CREATE USER [' + SL.name + '] FOR LOGIN [' + SL.name + '] WITH DEFAULT_SCHEMA = [dbo];' AS SQL_command
FROM sys.[syslogins] SL
        LEFT JOIN sys.[databases] SD
                ON SL.[dbname] = SD.[name]
WHERE SD.name IS NULL
ORDER BY SL.[name], SL.[dbname];
Листинг 2. Код для исправления
USE [master];
ALTER LOGIN [app_caribbean] WITH DEFAULT_DATABASE=[master];
CREATE USER [app_caribbean] FOR LOGIN [app_caribbean] WITH DEFAULT_SCHEMA = [dbo];

USE [master];
ALTER LOGIN [app_mediterranean] WITH DEFAULT_DATABASE=[master];
CREATE USER [app_mediterranean] FOR LOGIN [app_mediterranean] WITH DEFAULT_SCHEMA = [dbo];