Closure
Closure
Home » Databases » Microsoft SQL Server » How do we enable auditing in SQL Server?

How do we enable auditing in SQL Server?

How do we enable auditing in SQL Server?
, , , , Leave a comment
How do we enable auditing in SQL Server?
Latest posts by Stratos Matzouranis (see all)

In this article we will see the steps and options we have to activate it database auditing specification to SQL Server. Through the database auditing specification we can record who accesses or changes its data and how.

We can activate auditing on specific users or roles, on a specific database, on a specific schema or even on a specific object. We can also choose which actions will be recorded, e.g. deletes.

How is it activated?

To activate it, we must first create an instance of SQL Server server audit in which the information will be stored. With the following script, an audit server will be created with a physical file on the disk that we have chosen to be rolled over with a maximum size of 2GB:

USE master ;  
GO  
-- Create the server audit.  
CREATE SERVER AUDIT instance_audit  
TO FILE 
(	FILEPATH = N'C:\Audit'  -- make sure this path exists
	,MAXSIZE = 2 GB
	,MAX_ROLLOVER_FILES = 1
	,RESERVE_DISK_SPACE = ON
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)

GO
-- Enable the server audit.  
ALTER SERVER AUDIT instance_audit  WITH (STATE = ON) ;  
GO

Then we create in the database we want to enable auditing one database audit specification, where we define the auditing criteria. In this example, we set it to record all SELECT, INSERT, UPDATE, DELETE performed in the default schema dbo by all users (that is, of the public role):

*I only selected the dbo schema as if I selected the entire database it would also record accesses to system objects belonging to the sys schema. 

-- Move to the target database.  
USE AdventureWorks2019 ;  
GO  
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION [db_audit]
FOR SERVER AUDIT [instance_audit]
ADD (SELECT , INSERT , UPDATE, DELETE ON SCHEMA::[dbo] BY public )  -- all object for entire schema
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON DATABASE::[AdventureWorks2019] BY public )  -- all objects for entire database
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY public)  -- all users for specific object
-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY sql_login) -- specific user/role for specific object
WITH (STATE = ON)
GO

In the event that we want to activate auditing only for a specific user or role, or for a specific object, we can do this very easily by changing the lines that are in the comments in the example above.

How do we read the audit logs?

The easiest and most readable way to read the information is with the following query that I have made to read the information through the function sys.fn_get_audit_file.

With this query we can see:

  • When was it executed?
  • From which session id
  • From which login
  • On what basis
  • In which schema
  • In which object
  • Which query was executed
  • From which program?
  • From which computer name
  • And from which IP

We can also set criteria for the time, the table or some part of the query.

**The event_time field is in UTC timezone, so to put the real time of the server, we add the time difference between the server and the UTC timezone, which is done with the functions (DATEADD(HOUR, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME() ))

The query is the following:

declare @file_path nvarchar(260);
set @file_path = 
(select log_file_path+'*.sqlaudit'
from sys.server_file_audits
where name = 'instance_audit');


SELECT distinct
DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) AS [action_time],
session_id, session_server_principal_name,database_name,schema_name,object_name,statement,application_name,host_name,client_ip
FROM sys.fn_get_audit_file(@file_path, default, default)
WHERE 1=1
--and statement like '% from %'
--and DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) between '2022-11-03 13:00:00.000' and '2022-11-03 14:00:00.000'
--and object_name = 'test'
ORDER BY DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),event_time) desc;
How do we enable auditing in SQL Server?
01

How do we remove auditing?

To delete it, run the following query with the corresponding names:

USE AdventureWorks2019 ;  
GO  
ALTER DATABASE AUDIT SPECIFICATION [db_audit]
WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [db_audit];
GO
USE master
GO
ALTER SERVER AUDIT [instance_audit]
WITH (STATE = OFF)
GO
DROP SERVER AUDIT [instance_audit]
GO

Sources:

Share it

Leave a reply