Questions on database audit.

Question

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)

Answer 1

No one is exempted from audit.

Keep in mind that in SQL Server we have server principals and database principals, also commonly referred to as logins and users. The database audit specification is a database object, so naturally what you specify is a database user.

Commonly, when a server principal has access to a database, the name of the corresponding database principal is the same, but you are free to play around with this. And there are two situations when a server principal always maps to the database principal dbo:

• The server principal is the owner of the database.
• The server principal is member of the fixed server role sysadmin.

So if you want to track what a sysadmin user is doing, you need audit dbo.

Below is an example to illustrate. Note that the Audit file holds information about the server principal, so you can see which user who is hiding behind dbo

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) ;

CREATE LOGIN MyNewLogin WITH PASSWORD = 'MySameOldPassword'

CREATE DATABASE AuditTest
go
USE AuditTest
GO
CREATE TABLE MyNewTable (a int NOT NULL, b int NOT NULL)
GO

CREATE USER MyNewUser FOR LOGIN MyNewLogin
ALTER ROLE db_datareader ADD MEMBER MyNewUser
go
-- Fails with Cannot find the user 'MyNewLogin'
CREATE DATABASE AUDIT SPECIFICATION [dbAuditPII]
FOR SERVER AUDIT [auditPII]
ADD (SELECT ON OBJECT::[dbo].MyNewTable BY MyNewLogin)
WITH (STATE = ON)
GO

CREATE DATABASE AUDIT SPECIFICATION [dbAuditPII]
FOR SERVER AUDIT [auditPII]
ADD (SELECT ON OBJECT::[dbo].MyNewTable BY MyNewUser)
WITH (STATE = ON)
go

EXECUTE AS LOGIN = 'MyNewLogin'
go
SELECT SUSER_NAME(), USER
SELECT * FROM MyNewTable
go
REVERT
go
WAITFOR DELAY '00:00:05'
SELECT * FROM sys.fn_get_audit_file('C:\temp\bcptes\*', DEFAULT, DEFAULT)
go

USE master
go
ALTER SERVER ROLE sysadmin ADD MEMBER MyNewLogin
go
USE AuditTest
go
EXECUTE AS LOGIN = 'MyNewLogin'
go
SELECT SUSER_NAME(), USER
SELECT * FROM MyNewTable
go
REVERT
go
WAITFOR DELAY '00:00:05'
-- No new records this time.
SELECT * FROM sys.fn_get_audit_file('C:\temp\bcptes\*', DEFAULT, DEFAULT)
go
ALTER DATABASE AUDIT SPECIFICATION [dbAuditPII]  WITH (STATE = OFF)
go
ALTER DATABASE AUDIT SPECIFICATION [dbAuditPII]  FOR SERVER AUDIT [auditPII]
ADD (SELECT ON OBJECT::[dbo].MyNewTable BY dbo)
go
ALTER DATABASE AUDIT SPECIFICATION [dbAuditPII]  WITH (STATE = ON)
go
EXECUTE AS LOGIN = 'MyNewLogin'
go
SELECT SUSER_NAME(), USER
SELECT * FROM MyNewTable
go
REVERT
go
WAITFOR DELAY '00:00:05'
-- This time there is a new record.
SELECT * FROM sys.fn_get_audit_file('C:\temp\bcptes\*', DEFAULT, DEFAULT)
go
-- Cleanup time
ALTER DATABASE AUDIT SPECIFICATION dbAuditPII WITH (STATE = OFF)
go
USE master
go
ALTER SERVER AUDIT auditPII   
WITH (STATE = OFF) ;
go
DROP SERVER AUDIT auditPII   
DROP DATABASE AuditTest
DROP LOGIN MyNewLogin

Answer 2

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)