SQL Server Audit not tracking changes at the database level

Question

I'm currently trying to create a test implementation of SQL Server Audit in SQL Server Management Studio to see how it works. I created an test audit at the server level, and another one at the database level. In SQL Server, audits are disabled by default when they are first created, so I enabled both of them and tested to see if it worked. I updated several rows and deleted a role in a table that was in the tracked database. When I went to check the log, it didn't show any changes at the database level. I made a few changes (described below), and it did reflect those changes in the SQL Server Audit log.

First, I thought the log might need to be refreshed, so I refreshed the logs at both the database and server level. I restarted SQL Server Management Studio. Still wasn't working. I tried to specify which database to track at the server level by using the filter tool in the Properties menu by adding "([database_name]='test_database_name')".

Then, I thought maybe the problem was occurring at the database level, so I rechecked the configuration settings in the database audit properties menu. When selecting the Audit Action Types you want to track, you also have to set the Object Class, the Object Name, and the Principle. At first, I had set all of the Audit Action Types to track at the database level because I want to track all of the tables in the database, but I changed the Database to Object and selected all of the table names in the Object Name field. I'm not exactly sure what the Principal field does, but you have to select a particular user for the Principal. I started by selecting the Administrator role because I'm working on a test database, but I changed it to [sys]. None of these changes have had an effect.

Can anyone think of any reasons why the Server Audit log wouldn't be registering changes?

Answer 1

I think the main problem here - and please don't take offense - is that you're pointing and clicking in a GUI but not really sure what you're pointing and clicking at.

Here is an example that creates a server-level audit, then adds a database-level audit specification to track multiple operations on any object in the dbo schema.

USE master;
GO

-- create aserver audit
CREATE SERVER AUDIT Test_Server_Audit 
  TO FILE ( FILEPATH = 'C:\temp\' ); -- you may need to change that'
GO

-- turn it on
ALTER SERVER AUDIT Test_Server_Audit WITH (STATE = ON);
GO

-- create a demo database    
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.blat(x INT);
GO

-- create a database audit specification that monitors for activity
-- against any dbo object:
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY PUBLIC)
    WITH (STATE = ON);
GO

-- do a couple of things:
SELECT * FROM dbo.blat;
DELETE dbo.blat;
GO

-- you should see those couple of things in the audit file:
SELECT * FROM sys.fn_get_audit_file('C:\temp\*.sqlaudit', NULL, NULL);
GO

Now, clean up:

ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO

ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

I'd start at perhaps a higher conceptual level before pressing a bunch of buttons and hoping that they work.