{"id":4930,"date":"2023-05-03T07:00:00","date_gmt":"2023-05-03T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4930"},"modified":"2024-01-08T02:35:23","modified_gmt":"2024-01-07T23:35:23","slug":"pos-energopoioyme-to-auditing-se-sql-server","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-energopoioyme-to-auditing-se-sql-server\/","title":{"rendered":"How do we enable auditing in SQL Server?"},"content":{"rendered":"

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

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.<\/p>\n\n\n\n

How is it activated?<\/h2>\n\n\n\n

To activate it, we must first create an instance of SQL Server server audit<\/strong> 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:<\/p>\n\n\n\n

USE master ;  \nGO  \n-- Create the server audit.  \nCREATE SERVER AUDIT instance_audit  \nTO FILE \n(\tFILEPATH = N'C:\\Audit'  -- make sure this path exists\n\t,MAXSIZE = 2 GB\n\t,MAX_ROLLOVER_FILES = 1\n\t,RESERVE_DISK_SPACE = ON\n)\nWITH\n(\tQUEUE_DELAY = 1000\n\t,ON_FAILURE = CONTINUE\n)\n\nGO\n-- Enable the server audit.  \nALTER SERVER AUDIT instance_audit  WITH (STATE = ON) ;  \nGO  \n<\/pre>\n\n\n\n
Then we create in the database we want to enable auditing one database audit specification<\/strong>, 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):<\/p>\n\n\n\n
*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.<\/em> <\/p>\n\n\n\n
-- Move to the target database.  \nUSE AdventureWorks2019 ;  \nGO  \n-- Create the database audit specification.  \nCREATE DATABASE AUDIT SPECIFICATION [db_audit]\nFOR SERVER AUDIT [instance_audit]\nADD (SELECT , INSERT , UPDATE, DELETE ON SCHEMA::[dbo] BY public )  -- all object for entire schema\n-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON DATABASE::[AdventureWorks2019] BY public )  -- all objects for entire database\n-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY public)  -- all users for specific object\n-- ,ADD (SELECT , INSERT , UPDATE, DELETE ON dbo.AWBuildVersion BY sql_login) -- specific user\/role for specific object\nWITH (STATE = ON)\nGO\n<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
How do we read the audit logs?<\/h2>\n\n\n\n
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<\/code>.<\/p>\n\n\n\n
With this query we can see:<\/p>\n\n\n\n