November 21, 2022 at 8:04 pm
I'm wanting to use SQL Audit to monitor index create / drop / alter events. I can successfully do this for tables, stored procedures, users, and other objects using DATABASE_OBJECT_CHANGE_GROUP. However, it's not including DDL operations on indexes.
Anyone know if this is possible?
NOTE: It is possible to use DDL triggers, but I'd rather leverage SQL Audit for this if possible.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 22, 2022 at 1:06 pm
I'd give the "SCHEMA_OBJECT_CHANGE_GROUP" a try in your audit, that might do what you need. I'm not where I have access to a test instance I could verify this on, though, so I can't say 100% it's going to do what you need.
November 22, 2022 at 1:41 pm
I've already got SCHEMA_OBJECT_CHANGE_GROUP.
Here are all the audit groups I'm using:
ALTER DATABASE AUDIT SPECIFICATION [SQLAudit_$($database.name)_DDL]
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply