In a SQL Server Dev Edition 2019, I have set the following database audit specifications in a DB, via SSMS:

I do run the following query:

use [OmegaCA_Benchmark]

select t.*
from sys.database_audit_specification_details t

The result:

Problem:

No information at all on object names related info (except for class desc)

Somewhere on the web (not on Microsoft) I do find about using OBJECT_NAME, and do as below:

use [OmegaCA_Benchmark]

select t.*, OBJECT_NAME(t.major_id) as Obj_Name 
from sys.database_audit_specification_details t

The result:

Problem:

For Object Class OBJECT OR COLUMN - the schema name (OMEGACAEVAPP) is not displayed for object V_EMP. Another object with same name can exist in the same DB under another schema.

For Object Class SCHEMA - the schema name (sysrowsets) is wrong. The audited schema is OMEGACAEVAPP.

Question:

How can I have exact object information for database audit specification details?

In a SQL Server Dev Edition 2019, I have set the following database audit specifications in a DB, via SSMS:

I do run the following query:

use [OmegaCA_Benchmark]

select t.*
from sys.database_audit_specification_details t

The result:

Problem:

No information at all on object names related info (except for class desc)

Somewhere on the web (not on Microsoft) I do find about using OBJECT_NAME, and do as below:

use [OmegaCA_Benchmark]

select t.*, OBJECT_NAME(t.major_id) as Obj_Name 
from sys.database_audit_specification_details t

The result:

Problem:

For Object Class OBJECT OR COLUMN - the schema name (OMEGACAEVAPP) is not displayed for object V_EMP. Another object with same name can exist in the same DB under another schema.

For Object Class SCHEMA - the schema name (sysrowsets) is wrong. The audited schema is OMEGACAEVAPP.

Question:

How can I have exact object information for database audit specification details?

• sql-server
• t-sql

• 2 It's a per database table/view, so something like: case when class_desc = 'DATABASE' then db_name() END if you're in that database. To get object schema you can just go to sys.objects with that object_ID and get the schema_id. For pure schema, you probably need: SCHEMA_NAME(major_id). – siggemannen Commented Mar 31 at 18:54
• 2 Also, OBJECT_SCHEMA_NAME(major_id) can be used to get the schema name for an object in the current database. – Dan Guzman Commented Mar 31 at 20:35

2 Answers 2

You can run a trace and open the database audit specification UI to see the query that SSMS sends to retrieve the information that it shows in the UI.

FWIW this (reformatted) is below

SELECT [AuditActionType] = details.audit_action_name,
       [ObjectClass] = ISNULL(CASE WHEN s.class_desc != 'DATABASE' AND s.class_desc != 'SCHEMA' THEN 'OBJECT' ELSE s.class_desc END, ''),
       [ObjectSchema] = ISNULL(SCHEMA_NAME(o.schema_id), ''),
       [ObjectName] = ISNULL(CASE WHEN details.is_group = 0 AND details.class_desc = 'DATABASE' THEN db_name() WHEN details.class_desc = 'SCHEMA' THEN sch.name ELSE o.name END, ''),
       [Principal] = ISNULL(p.name, '')
FROM   sys.database_audit_specifications AS das
       INNER JOIN sys.database_audit_specification_details AS details
               ON details.database_specification_id = das.database_specification_id
       LEFT OUTER JOIN sys.securable_classes AS s
                    ON details.is_group = 0
                       AND s.class = details.class
       LEFT OUTER JOIN sys.all_objects AS o
                    ON details.is_group = 0
                       AND o.object_id = details.major_id
                       AND details.class_desc != 'SCHEMA'
                       AND details.class_desc != 'DATABASE'
       LEFT OUTER JOIN sys.schemas AS sch
                    ON details.is_group = 0
                       AND sch.schema_id = details.major_id
                       AND details.class_desc = 'SCHEMA'
       LEFT OUTER JOIN sys.database_principals AS p
                    ON details.is_group = 0
                       AND p.principal_id = details.audited_principal_id
WHERE  das.name = N'YourDatabaseAuditSpecificationName' 

If you would rather use the object metadata functions than joins then you should be able to use

   CASE
     WHEN details.is_group = 0
          AND details.class_desc = 'DATABASE' THEN db_name(details.major_id)
     WHEN details.class_desc = 'SCHEMA' THEN SCHEMA_NAME(details.major_id)
     ELSE OBJECT_NAME(details.major_id)
   END 

To get the schema name/database name/ object name as appropriate to the class.

For ObjectSchema you could also use something like

OBJECT_SCHEMA_NAME(CASE
                    WHEN class_desc NOT IN ('SCHEMA','DATABASE') THEN major_id
                    END) 

Thanks to siggermannen and Dan Guzman I made it to the following query:

use [OmegaCA_Benchmark]

select 
a.database_specification_id,
a.audit_action_id, a.audit_action_name, 
a.class, a.class_desc, 
a.major_id,
object_schema_name = 
CASE 
    WHEN a.class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_SCHEMA_NAME(a.major_id)
    ELSE NULL
END,
object_name = 
CASE 
    WHEN a.class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(a.major_id)
    WHEN a.class_desc = 'SCHEMA' THEN SCHEMA_NAME(a.major_id)
    WHEN a.class_desc = 'DATABASE' THEN 'OmegaCA_Benchmark'
    ELSE NULL
END,
a.minor_id,
a.audited_principal_id, c.name as Principal_Name,
a.audited_result, 
a.is_group,
b.name as DB_Aud_Spec_Name, 
b.create_date, b.modify_date, 
b.audit_guid, 
b.is_state_enabled

from sys.database_audit_specification_details a

inner join sys.database_audit_specifications b
on a.database_specification_id = b.database_specification_id

inner join sys.database_principals c 
on a.audited_principal_id = c.principal_id

best regards
Altin