При планировании аварийного восстановления критически важно обеспечить правильное владение всеми базами данных SQL Server. Хотя не рекомендуется иметь базы данных, владельцем которых являются учетные записи, отличные от учетной записи sa, есть сценарии, когда определенным учетным записям необходимо быть назначенными владельцами. В таких случаях важно иметь скрипт, который может установить правильных владельцев для баз данных, восстановленных после аварии.

Настройка примера

Давайте начнем с настройки примера, чтобы понять, как можно получить необходимую информацию из каталоговых представлений sys.databases и sys.server_principals:

CREATE DATABASE Test1;
CREATE DATABASE Test2;
CREATE DATABASE Test3;

CREATE LOGIN TestLogin1 WITH PASSWORD = 'MakeItAStr0ng0ne!';
CREATE LOGIN TestLogin2 WITH PASSWORD = 'MakeItAStr0ng0ne!';
CREATE LOGIN TestLogin3 WITH PASSWORD = 'MakeItAStr0ng0ne!';

ALTER AUTHORIZATION ON DATABASE::Test1 TO TestLogin1;
ALTER AUTHORIZATION ON DATABASE::Test2 TO TestLogin2;
ALTER AUTHORIZATION ON DATABASE::Test3 TO TestLogin3;

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

Скрипт владельцев баз данных SQL Server

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

SELECT 'IF EXISTS(SELECT name FROM sys.server_principals WHERE name = ''' 
  + P.name + ''') ALTER AUTHORIZATION ON DATABASE::[' + D.name +
  '] TO [' + P.name + '];
GO'
FROM sys.databases D
  JOIN sys.server_principals P
    ON D.owner_sid = P.sid
WHERE D.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY D.name;

Выполнение вышеуказанного скрипта сгенерирует T-SQL-инструкции для изменения владения каждой пользовательской базы данных.

Автоматизация запроса скрипта

Для автоматизации процесса генерации скрипта владения вы можете сохранить запрос в файл .sql и использовать SQLCMD для выполнения запроса и сохранения результатов.

sqlcmd -S localhost -E -i "путь\к\скрипту.sql" -o "путь\к\выходному_файлу.sql"

Этот пример демонстрирует выполнение SQLCMD на локальном сервере с использованием аутентификации Windows. Параметр -i указывает входной файл с запросом, а параметр -o указывает выходной файл, в котором будут сохранены скрипты владельцев.

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

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