Questions on database audit.

Question

Questions on database audit.

chrisrdba 451

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)

0 comments

2 answers

Your answer

Answer 1

Erland Sommarskog 138.7K MVP Volunteer Moderator

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)

chrisrdba 451 Reputation points

2023-07-21T22:05:37.9666667+00:00

After beating my head a while I realized the issue is that "myLogin" is in the SA role, "abaauto" is only in the db_reader role. Adding and removing my myLogin from SA will result in the action being logged or not.

Really shocked this is how it works, as it leaves SA's exempt from audits.

Does anyone know how to turn off this "feature", and make it so SA's aren't exempt from audits?
Erland Sommarskog 138.7K Reputation points MVP Volunteer Moderator

2023-07-21T22:10:45.24+00:00

I don't have the time check now (as I just crawling off to bed), but since it is on database level, I guess what you specify in the clause is a database user. And if you are sysadmin, your login maps to dbo in all databases.
chrisrdba 451 Reputation points

2023-07-24T14:43:34.81+00:00

Agreed, but why would SA (or DBO for that matter) be exempt from audits?

To be clear -- I add this login to SA, new records do NOT get added to the audit file when this user runs a SELECT.

I drop from SA, new records do get added to the audit file when this user runs a SELECT.

Why would anyone want an audit that doesnt include the people that have SA rights? Better yet is there a way to disable this?

Thanks!
chrisrdba 451 Reputation points

2023-07-24T19:29:23.35+00:00

This makes sense and works as expected, thanks!

Is there a way to mark your last comment as the Accepted Answer?
Erland Sommarskog 138.7K Reputation points MVP Volunteer Moderator

2023-07-24T19:31:25.8566667+00:00

There is now. I've converted it to an Answer.

Answer 2

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