Database-audit-step-by-step

We can use fn_get_audit_file function to open an audit file in command line.

---------------------------------------------------------------------------------------------------------------------------------------------

SELECT DATEADD(ss,DATEDIFF(ss,GETUTCDATE(),CURRENT_TIMESTAMP),event_time)[event time],action_id,class_type,session_server_principal_name,database_principal_name, database_name,object_name,statement,host_name

FROM fn_get_audit_file('C:\SQLBACKUP\*.sqlaudit',NULL,NULL)

ORDER BY event_time DESC

Steps to configure database audit :

Step - 1 : Right click on Security-->Audits-->New Audit..   ,Define an Audit name as you want (In below figure green rectangle indicates the Audit name).

Step - 2 : Click on three dots(...) and set a path for audit file as shown in figure below then click OK.

Step - 3 : Now go to any database and Right click on Security-->Database Audit Specifications then click on New Database Audit Specification... as shown in figure below.

Step - 4 : Now set any filename and then choose Audit(Highlighted with arrow) which you have created in Step-1, After that select the Audit Action Type as per your requirement (In my case I have selected 5 Types as shown in below figure) then click on OK.

Note : In below figure I have highlighted two points on Principal Name column , the Red highlight is defining the Public that means you can track all users for defined Audit Action Type but incase if you want to track a particular user then select the user in Principal Name column instead of Public as I have highlighted in Green.

Step - 5 : Now you can see an AuditSpecification is created under Database Audit Specification ,Right click on that and enable it by clicking on Enable Database Audit Specification option (In below figure green rectangle indicates the same).

Step - 6 : Now enable the Audit (That you have created in Step-1 under Security-->Audit), Right click on Audit then click on Enable Audit.

In the above steps our database level audit has been configured, Now if any user perform any activity (Belongs to Audit Action Type) will captured in Audit file. To view the Audit-Logs (what user has done the activity) Right click on audit file then click on View Audit Logs option (In below figure I have defined the same in green rectangle), now you can see the users activity.