Audit Made Easy Without Audit Log - Part One

Bhuvanesh Mohankumar
Dec 07, 2017
33.6k
0
3
Article

Introduction

In Microsoft SQL Server, the activity of each of the database tables is tracked in the other table. The phenomenon is called the Audit trail or Audit log of the database table.

In most of the cases, we don’t explicitly create the “Server Audit”. The reasons for not having a server audit for the database are listed below,

More memory space consumption
Each of the CRUD operations will trigger the Insert operation into Audit record which slows the process
Query execution time is increased [Not directly impacted]
Expensive

In this article, I will explain how to make use of the existing resources in the SQL Server which cannot directly replace the Audit trail whereas it can help to solve a few of the needs which an Audit trail can satisfy.

The Microsoft SQL Server itself has some of the features which were not used by the developers frequently and I am going to explore these options.

SQL Server Audit (Database Engine)

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server Audit lets you create server audits, which can contain server audit specifications for server level events and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.

There are several levels of auditing for SQL Server, depending on government or standard requirements for your installation. SQL Server Audit provides the tools and processes you need to enable, store, and view audits on various servers and database objects.

Reference

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

Scenario 1

I have to identify the last table updated with DateTime, however, in this scenario, the Audit trail is not available for the specific table. Also, there is no column in the database table which shows the Last updated date time.

We are going to use the system table of SQL Server where some database and data table information is stored. sys.dm_db_index_usage_stats is the table which is used to find the value of the table updated last time.

I will explain about the table “sys.dm_db_index_usage_stats” in later parts of this article but now, let’s know the usage of the table in different scenarios.

Query

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,
last_user_update
FROM sys.dm_db_index_usage_stats

Output/Result

Output

Scenario 2

In this, we have to get the Date time when the table was last executed with SELECT query. This will be helpful to check if some are used in an application or Code or by its stored procedures for a long time, if not, we can drop the table which is not necessary.

To confirm the usage of the table, we can use the following query. In this sample, I have added the condition to check for a specific data table value.

Query

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_seekFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('ApplicationConfiguration')

Output/Result

Output

Here, the “ApplicationConfiguration” table name is specified in the query. So, the information is shown only of that table.

Scenario 3

In this scenario, we have to find the number of times a table is used for Read or Update operations.

This is very helpful for the analytics scenarios where a certain table may be used multiple times, whereas some other tables may not be used at all by the application. So, to identify the frequency of usage, we can use this query.

Query - Number_of_Select_Queries

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName,user_seeks AS Number_of_Select_QueriesFROM sys.dm_db_index_usage_stats

Output/Result

Output

Query - Number_of_Update_Queries

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, user_updates AS Number_of_Update_QueriesFROM sys.dm_db_index_usage_statsWHERE OBJECT_ID=OBJECT_ID('Logs')

Output/Result

Output

The usage of this system table is not just limited to the above scenarios. It has many columns and each column is helpful for the user to get the statistics and index based reporting the data base is not configured with action audit.

More information on “sys.dm_db_index_usage_stats”

sys.dm_db_index_usage_stats has a limitation that it does not return the details about memory-optimized indexes, however, we have other set of alternatives to achieve those tasks.

I have provided the information of each of the Columns.

Query

SELECT *FROM sys.dm_db_index_usage_stats

The below result value shows all the information for each table in the particular database.

Result

Output

The identifier for the node that this distribution is ON.

Table Column name	Data type	Information
database_id	smallint	Database ID on the table is defined.
object_id	int	ID of the table the index is defined
index_id	int	ID of the index.
user_seeks	bigint	Number of seeks by user queries.
user_scans	bigint	Number of scans by user queries. This represents scans that did not use 'seek' predicate.
user_lookups	bigint	Number of bookmark lookups by user queries.
user_updates	bigint	Number of updates by user queries. This includes Insert, Delete and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count will increment by 1
last_user_seek	datetime	Time of last user seek
last_user_scan	datetime	Time of last user scan.
last_user_lookup	datetime	Time of last user lookup.
last_user_update	datetime	Time of last user update.
system_seeks	bigint	Number of seeks by system queries.
system_scans	bigint	Number of scans by system queries.
system_lookups	bigint	Number of lookups by system queries.
system_updates	bigint	Number of updates by system queries.
last_system_seek	datetime	Time of last system seek.
last_system_scan	datetime	Time of last system scan.
last_system_lookup	datetime	Time of last system lookup.
last_system_update	datetime	Time of last system update.
pdw_node_id	int	Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse