Идентификация пользователя, создавшего объекты в SQL

Быстрый ответ

Чтобы узнать, кто создал объекты в SQL Server, можно воспользоваться сочетанием представлений sys.objects, sys.schemas и sys.database_principals. Столбец principal_id связывает объекты с их владельцами. Рассмотрим фрагмент SQL-запроса:

SELECT o.name AS Object, dp.name AS Creator, o.create_date
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE o.type IN ('U', 'P', 'V')
ORDER BY o.create_date;

Этот запрос представляет информацию о названии объекта, создателе и дате создания. Если нужно ограничить выборку, можно модифицировать условие в WHERE.

Вглубь деталей: ищем потайные места создания объектов

Отчёт История изменений схемы, доступный в SQL Server Management Studio, содержит подробности всех DDL-операций, позволяя определить время и пользователя, связанные с созданием, изменением или удалением объектов.

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

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

Важно учесть, что если пользователь обладает статусом dbo или тесно связан с владельцем схемы, значение principal_id может не отражать реального создателя объекта.

Визуализация

Процесс определения создателя объектов в SQL Server можно визуализировать следующим образом:

Представьте себе пляж (🏖️) с отпечатками ног (👣):

| Объект           | Отпечаток Создателя |
| ---------------- | ------------------- |
| Таблица          | 👣 от Пользователя А |
| Хранимая процедура | 👣 от Пользователя B |
| Представление    | 👣 от Пользователя А |
| Функция          | 👣 от Пользователя C |

Все действия по созданию объекта оставляют уникальный след, указывающий на его автора.

SELECT 
    name AS Object,
    create_date AS CreationTime,
    modify_date AS LastModifiedTime
FROM 
    sys.objects
WHERE 
    type IN ('U', 'P', 'V', 'FN', 'IF', 'TF')

Запрос представления sys.objects помогает обнаружить следы создателей, связанные с каждым объектом.

Системный подход: советы по изучению истории

Если нет значений для principal_id или используются устаревшие версии SQL Server, такие как 2005, можно воспользоваться связкой таблиц sysobjects и sysusers:

SELECT o.name, o.crdate AS CreationDate, u.name AS Creator
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
ORDER BY o.crdate;

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

Инструменты сторонних разработчиков, вроде sp_whoisactive от Адама Механика, предлагают динамическую картину текущих изменений в реальном времени, но они не хранят историю.

Инструменты Change Data Capture и SQL Server Audit, доступные в новых версиях, обеспечивают более детальный анализ активности пользователей, что важно для соответствия требованиям аудита и регулирования.

Полезные материалы

1. SQL-запрос для поиска всех разрешений/доступов для всех пользователей в базе данных – Stack Overflow — полезная информация о управлении правами пользователей.
2. Хранимая процедура мониторинга SQL Server sp_whoisactive от Адама Механика — бесплатный инструмент для мониторинга активности SQL Server.
3. Просмотр и анализ трассировок – SQL Server | Microsoft Learn — подробное руководство от Microsoft по использованию SQL Server Profiler.
4. Что такое захват изменений данных (CDC)? – SQL Server | Microsoft Learn — подробное описание CDC и его использования для отслеживания изменений.
5. Представления каталога безопасности (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация Microsoft о представлениях каталога безопасности.