Изображение от vectorpouch на Freepik

Первый способ это создание триггеров и запись значений в таблицу, но мне такое не нравиться и здесь писать об этом я не буду, тем более у меня есть лицензия Enterprise.

Мы же рассмотрим вариант с использованием Server Audit Specifications, но только если у вас лицензия Enterprise.

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

SQL Server Audit

Для начала нам потребуется создать объект Audit, вы можете создавать несколько объектов. Чтобы было проще это просто настройка как и где хранить события аудита.

Для того чтобы создать Audit переходим в Security > Audit > New audit.

  • Queue delay (in milliseconds) - Указывает время в миллисекундах, которое может пройти до принудительной обработки действий аудита. Значение 0 указывает на синхронную доставку. Минимальное значение по умолчанию — 1000 (1 секунда)
  • On Audit Log Failure - Тут вы указываете что делать если вдруг события аудита не сохраняются.
  • Audit destination - Тут мы выбираем куда именно сохранять события аудита. Можно выбрать журнал Windows или просто локальную/сетевую директорию. Рекомендуется выбирать вариант с директорией, так как временами слишком много событий, которые будут вам мешать отлавливать другие события. Хотя с помощью прав можно например запретить чистку журнала Windows, что гарантирует сохранность логов.
  • Maximum file size - максимальный размер файла с событиями (если сохранять в файл)

ms sql Server Audit create

Либо можно воспользоваться запросом :

USE [master]
GO

CREATE SERVER AUDIT [Test_Server_Audit] --<< [Test_Server_Audit] must be changed
TO FILE 
(	FILEPATH = N'G:\Databases\'  --<< must be changed
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE, AUDIT_GUID = '23758ca3-efb5-4add-97d6-9b099b3c7acf')
ALTER SERVER AUDIT [Test_Server_Audit] WITH (STATE = ON) --<< [Test_Server_Audit] must be changed
GO

Database audit specification

После того как мы создали аудит (где хранить и как) нужно указать что именно мы хотим мониторить. Прямо сейчас мы рассматриваем аудит действий с конкретной БД, действия с сервером рассмотрим ниже.

Для того чтобы создать Database audit specification переходим Databases > db_name > Security > Database Audit Specificationc > New.

  • Audit - тут выбираем аудит, который мы создали выше.
  • Audit Action Type- Список что именно нужно включить в аудит. Список групп и их предназначение можно глянуть тут или тут.

ms sql Database audit specification create

Благодаря таким настройкам я могу просмотреть что выполнял пользователи test или любой пользователь с правами sysadmin (dbo) при подключении к бд floob.

Либо можно воспользоваться запросом :

USE master;
GO

CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit --<< Test_Database_Audit must be changed
    FOR SERVER AUDIT Test_Server_Audit --<< Test_Server_Audit must be changed
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY test),
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY dbo)
    WITH (STATE = ON);
GO

Как проверить

Во первый нужно от пользователя test выполнить запросы к бд, в моём случае это простой select. После чего в зависимости от того что вы выбрали в качестве хранилища для аудита (файл или журнал Windows) получить эти события.

Если вы храните в файлах, то они будут с расширением .sqlaudit.

Для того чтобы получить доступ к содержимому можно воспользоваться Sql запросом:

SELECT * FROM sys.fn_get_audit_file('G:\Databases\*.sqlaudit', NULL, NULL) where database_name='floob';

2023-08-25 09:38:18.7476766	test	test	0x431B167D6AC2BF4AB13E57F6AD373C65	test		NULL		WIN-OCK9TD7MSK7	floob	SELECT TOP (1000) [x]    FROM [floob].[dbo].[dasdas]		G:\Databases\Test_Server_Audit_8B5A85A9-5B42-4D7A-8B2C-CE807711C928_0_133374297948610000.sqlaudit

Server audit specification

По сути тут всё тоже самое как и в Database audit specification только на уровне сервера.

Для того чтобы создать Server audit specification переходим Security > Server Audit Specificationc > New.

  • Audit Action Type- Список что именно нужно включить в аудит. Список групп и их предназначение можно глянуть тут или тут.

ms sql server audit specification create

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Test_Server_Audit] --<< Test_Server_Audit must be changed
FOR SERVER AUDIT [Test_Server_Audit] --<< Test_Server_Audit must be changed
ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON)
GO

ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO

SELECT * FROM sys.fn_get_audit_file('G:\Databases\*.sqlaudit', NULL, NULL) where statement like '%AUDIT%' ;
GO

2023-08-25 11:06:57.9158665			Test_Database_Audit	ALTER DATABASE AUDIT SPECIFICATION [Test_Database_Audit]  WITH (STATE = OFF)		G:\Databases\Test_Server_Audit_23758CA3-EFB5-4ADD-97D6-9B099B3C7ACF_0_133374313105990000.sqlaudit