1

I'm studying about SQL Server Audit. I have deployed Server Audit Specification. Now I want to query all the records but It doesn't return anything.

I use Windows Server 2012 Datacenter - SQL Server 2014 Developer Version

use master
go
select *
from sys.database_audit_specifications;
go

I got no output and don't understand why.

How can I fix it?

Image

Arulkumar
  • 12,966
  • 14
  • 47
  • 68
mtpumpkin
  • 21
  • 2
  • Hi can you show how you specified the Server Audit Specifications. in Create audit box – AmilaMGunawardana May 26 '19 at 01:34
  • Hi, I created Server Audit Specifications by using GUI. I config Server Audit Specifications log for login fail and query such as select, insert, update, delete,... – mtpumpkin May 27 '19 at 03:37

1 Answers1

0

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

For Further Reading follow this

AmilaMGunawardana
  • 1,604
  • 2
  • 13
  • 32