Questions on database audit.

chrisrdba 451 Reputation points
2023-07-20T16:54:09.2133333+00:00

Does anyone know why my DB audit isn't collecting any data? 

USE master ;  
GO  
-- Create the server audit.   
CREATE SERVER AUDIT auditPII
    TO FILE ( FILEPATH =   
'D:\perflogs\auditPII' ) ;   
GO  
-- Enable the server audit.   
ALTER SERVER AUDIT auditPII   
WITH (STATE = ON) ;

USE [myDB]
GO

DROP DATABASE AUDIT SPECIFICATION [dbAuditPII]
GO

CREATE DATABASE AUDIT SPECIFICATION [dbAuditPII]
FOR SERVER AUDIT [auditPII]
ADD (SELECT ON OBJECT::[dbo].[myTable] BY [myLogin]),
ADD (SELECT ON OBJECT::[dbo].[myTable] BY [myOtherLogin])
WITH (STATE = ON)
GO

execute as login = 'myLogin'

select SUSER_NAME()
--myLogin verified

select top 1000 *
from myDB..myTable


SELECT * FROM fn_get_audit_file('D:\perflogs\auditPII\*',default,default)
SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

2 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Erland Sommarskog 138.7K Reputation points MVP Volunteer Moderator
    2023-07-20T22:10:07.7333333+00:00

    I ran the below, and I see audit records for my test user in the file.

    I note that in your script, there is no REVERT before you query sys.fn_get_audit_file.

    USE master ;  
GO  
-- Create the server audit.   
CREATE SERVER AUDIT auditPII
    TO FILE ( FILEPATH =   
'C:\temp\bcptes' ) ;   
GO  
-- Enable the server audit.   
ALTER SERVER AUDIT auditPII   
WITH (STATE = ON) ;

USE Northwind
GO

DROP DATABASE AUDIT SPECIFICATION [dbAuditPII]
GO

CREATE USER abaauto
ALTER ROLE db_datareader ADD MEMBER abaauto

CREATE DATABASE AUDIT SPECIFICATION [dbAuditPII]
FOR SERVER AUDIT [auditPII]
ADD (SELECT ON OBJECT::[dbo].[Orders] BY abaauto)
WITH (STATE = ON)
GO

execute as login = 'abaauto'

select SUSER_NAME()
--myLogin verified

select top 1000 *
from Orders
go
REVERT

SELECT * FROM fn_get_audit_file('C:\temp\bcptes\*',default,default)

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.