SQL Server Audit (Server Audit)

Views: 13.422 views

In this article

This post is the 1 part of 20 in the series. Security and Audit

Reading Time: 4 minutes

Hello guys!
Good night!

In this post I will demonstrate how to create Server Audit in SQL Server. This feature is very interesting for auditing DDL and / or DML (Database Audit Specification) actions performed by a user or application or even the instance itself (Server Audit Specification).

Creating an interface audit (GUI)

Open SQL Server Management Studio, select the category Security> Audits> Right click> Select the option “New Audit…”

When you set auditing, recorded data can be saved in 3 ways:

File: A physical file is generated on disk containing the data collected by the audit.
Security Log: Data collected by the audit is stored in the server security log.
Application Log: Data collected by the audit is stored in the server application log.

In this post, I will choose to burn to physical file on disk. By selecting this option you can still specify the file name where it will be saved, the maximum size and choose whether you already want to pre-reserve this maximum disk space (ie if you choose the maximum size of 10 GB, SQL Server will already create this file with allocated space 10 GB)

After creating the Audit, you must enable it by right clicking on it in the Object Explorer and then selecting the option “Enable Audit”

Creating a server-level audit (Server Audit Specification)

Now that the audit object has been created, we can create the audit definition object (Server Audit Specification).

Select the instance and select the Security category> Server Audit Specification> Right click> Select the “New Server Audit Specification” option

In this screen, you can define what you want to monitor, as shown below:

View all types of server level audits

Action Group Name	Description
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP	This event is raised whenever a password is changed by an application role. Equivalent to Audit App Role Change Password Event Class.
AUDIT_CHANGE_GROUP	This event is raised whenever an audit is created, modified, or deleted. This event is raised whenever any audit specification is created, modified, or deleted. Any change to an audit is audited in that audit. Equivalent to Audit Change Audit Event Class.
BACKUP_RESTORE_GROUP	This event is raised whenever a backup or restore command is issued. Equivalent to Audit Backup / Restore Event Class.
BROKER_LOGIN_GROUP	This event is raised to report Service Broker transport security-related audit messages. Equivalent to Audit Broker Event Class Login.
DATABASE_CHANGE_GROUP	This event is raised when a database is created, altered, or dropped. This event is raised whenever a database is created, altered, or dropped. Equivalent to Audit Database Management Event Class.
DATABASE_MIRRORING_LOGIN_GROUP	This event is raised to report audit messages related to database mirroring transport security. Equivalent to Audit Database Mirroring Event Class Login.
DATABASE_OBJECT_ACCESS_GROUP	This event is raised whenever database objects such as message type, assembly, contract are accessed. It is generated for any access to any database. Observation This could potentially generate large audit records. Equivalent to Audit Database Object Access event class.
DATABASE_OBJECT_CHANGE_GROUP	This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas. It is generated whenever a database object is created, altered, or dropped. Observation This could lead to very large volumes of audit records. Equivalent to Audit Database Object Management Event Class.
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP	This event is raised when there is an owner change for objects within the scope of the database. It is generated for any object ownership change in any database on the server. Equivalent to Audit Database Object Take Ownership Event Class.
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP	This event is raised when GRANT, REVOKE, or DENY is raised for database objects such as assemblies and schemas. It is generated for any object permission changes in any database on the server. Equivalent to Audit Database Object GDR event class.
DATABASE_OPERATION_GROUP	This event is raised when operations against a database occur, such as a checkpoint or subscription query notification. This event is raised on any database operation in any database. Equivalent to Audit Database Operation event class.
DATABASE_OWNERSHIP_CHANGE_GROUP	This event is raised when the ALTER AUTHORIZATION statement is used to change the owner of a database and the permissions required to do so are marked. It is generated for any database ownership change on any database on the server. Equivalent to Audit Change Database Owner Event Class.
DATABASE_PERMISSION_CHANGE_GROUP	This event is raised whenever GRANT, REVOKE, or DENY is issued for a statement permission by any entity in SQL Server (This applies to database-only events, such as granting permissions on a database, for example.). This event is raised for any database permission change (GDR) of any database on the server. Equivalent to Audit Database Scope GDR event class.
DATABASE_PRINCIPAL_CHANGE_GROUP	This event is raised when entities, such as users, are created, changed, or dropped from a database. Equivalent to Audit Database Principal Management Event Class. (Also equivalent to the Audit Add DB Principal event class, which occurs in the replaced sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal, and sp_dropPrincipal stored procedures.) This event is raised whenever a database role is added or removed through the sp_addrole and sp_droprole stored procedures. This event is raised whenever any database entity is created, altered, or dropped from any database. Equivalent to Audit Add Role Event Class.
DATABASE_PRINCIPAL_IMPERSONATION_GROUP	This event is generated when there is a representation operation at the database scope, such as EXECUTE AS or SETPRINCIPAL. It is generated for representations made in any database. Equivalent to Audit Database Main Event Class Impersonation.
DATABASE_ROLE_MEMBER_CHANGE_GROUP	This event is raised whenever a login is added or removed from a database role. This event class is generated for the sp_addrolemember, sp_changegroup, and sp_droprolemember stored procedures. This event is raised on any change to the Database role member in any database. Equivalent to Audit Add Member to DB Role Event Class.
DBCC_GROUP	This event is raised whenever an entity issues any DBCC commands. Equivalent to Audit DBCC event class.
FAILED_LOGIN_GROUP	Indicates that an entity attempted to log on to SQL Server and failed. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Login Failed Event Class.
FULLTEXT_GROUP	Indicates that a full text event has occurred. Equivalent to Audit Fulltext Event Class.
LOGIN_CHANGE_PASSWORD_GROUP	This event is raised whenever a login password is changed by the ALTER LOGIN statement or the sp_password stored procedure. Equivalent to Audit Login Change Password Event Class.
LOGOUT_GROUP	Indicates that an entity has logged off to SQL Server. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Logout Event Class.
SCHEMA_OBJECT_ACCESS_GROUP	This event is raised whenever an object permission is used on the schema. Equivalent to Audit Schema Object Access event class.
SCHEMA_OBJECT_CHANGE_GROUP	This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema. Equivalent to Audit Schema Object Management event class. This event is raised on schema objects. Equivalent to Audit Object Derived Permission Event Class. This event is raised whenever any schema in any database is changed. Equivalent to Audit Statement Permission Event Class.
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP	This event is raised when permissions to change the schema object owner (such as a table, procedure, or function) are checked. This occurs when the ALTER AUTHORIZATION statement is used to assign an owner to an object. This event is raised for any schema property changes in any server database. Equivalent to Audit Schema Object Take Ownership Event Class.
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP	This event is raised whenever a grant, deny, or revoke is performed against a schema object. Equivalent to Audit Schema Object GDR event class.
SERVER_OBJECT_CHANGE_GROUP	This event is raised for CREATE, ALTER, or DROP operations on server objects. Equivalent to Audit Server Object Management Event Class.
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP	This event is raised when the owner changes to server-scoped objects. Equivalent to Audit Server Object Take Ownership Event Class.
SERVER_OBJECT_PERMISSION_CHANGE_GROUP	This event is raised when GRANT, REVOKE, or DENY is issued for a server object permission on any entity in SQL Server. Equivalent to Audit Server Object GDR event class.
SERVER_OPERATION_GROUP	This event is raised when security audit operations such as changing settings, features, external access, or authorization are used. Equivalent to Audit Server Operation Event Class.
SERVER_PERMISSION_CHANGE_GROUP	This event is raised when GRANT, REVOKE, or DENY is issued for server scope permissions, such as creating a login. Equivalent to Audit Server Scope GDR event class.
SERVER_PRINCIPAL_CHANGE_GROUP	This event is raised when server entities are created, changed, or dropped. Equivalent to Audit Server Principal Management Event Class. This event is raised when an entity issues sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements. Equivalent to Audit Addlogin Event Class. This event is raised in the sp_addlogin and sp_droplogin stored procedures. Also equivalent to Audit Login Change Property Event Class. This event is raised for the sp_grantlogin, sp_revokelogin, or sp_denylogin stored procedures. Equivalent to Audit Login GDR Event Class.
SERVER_PRINCIPAL_IMPERSONATION_GROUP	This event is raised when there is a representation within the scope of the server, such as EXECUTE AS . Equivalent to Audit Server Principal Impersonation Event Class.
SERVER_ROLE_MEMBER_CHANGE_GROUP	This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. Equivalent to Audit Event Class Add Login to Server Role.
SERVER_STATE_CHANGE_GROUP	This event is raised when the state of the SQL Server service is modified. Equivalent to Audit Server Starts and Stops Event Class.
SUCCESSFUL_LOGIN_GROUP	Indicates that the entity was successfully logged on to SQL Server. Events in this class are generated by new connections or reused connections from a connection pool. Equivalent to Audit Login event class.
TRACE_CHANGE_GROUP	This event is raised for all statements that check the ALTER TRACE permission. Equivalent to Audit Server Alter Trace event class.

After choosing the audit types (in this example, I want to audit database creation / deletion), click OK and enable the audit specification:

Creating a database audit (Database Audit Specification)

A very nice feature that can often replace the use of triggers is the creation of database level audit routines. In this example, I will create an audit to record changes made to a specific table.

To create a database-level audit, select the instance and then the database to be audited, expand the Security category, select the Database Audit Specifications sub-category, right-click and select the “New Database Audit Specification ”

In this screen we can define what we want to audit in this database.

In the above configuration, I set to audit the following operations:
- DELETEs performed in the Tests database by the user “Usuario_Teste”
- INSERTs made in the Customers table made by any user.
- INSERTs on any object in the dbo schema, made by any user.

After performing the configuration, remember to activate the audit by right clicking on it and selecting the option “Enable Database Audit Specification” and your audit is activated!

Viewing collected data

To view the data that was collected by the Audit, just right-click on the Audit (not the Specification) and select the option “View Audit Logs”

Creating a command line audit

After all this post explaining how to create database and server audits using the SQL Server Management Studio screens, I will briefly explain how to create these same audits via command line, which is a much more practical way to replicate across multiple servers and fix more. knowledge of the DBA.

With the commands below, I already create auditing (and active), server and database auditing (both already enabled as well).

USE [master]
GO

CREATE SERVER AUDIT [Auditoria_Arquivo]
TO FILE 
(	FILEPATH = N'C:\'
	,MAXSIZE = 100 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
GO

ALTER SERVER AUDIT Auditoria_Arquivo WITH (STATE = ON)


CREATE SERVER AUDIT SPECIFICATION [Criação de Banco de Dados]
FOR SERVER AUDIT [Auditoria_Arquivo]
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON)
GO


USE [Testes]
GO

CREATE DATABASE AUDIT SPECIFICATION [Audita_DML]
FOR SERVER AUDIT [Auditoria_Arquivo]
ADD (DELETE ON DATABASE::[Testes] BY [Usuario_Teste]),
ADD (INSERT ON SCHEMA::[dbo] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[Clientes] BY [public])
WITH (STATE = ON)
GO

USE [master]

CREATE SERVER AUDIT [Auditoria_Arquivo]

TO FILE

( FILEPATH = N'C:\'

,MAXSIZE = 100 MB

,MAX_ROLLOVER_FILES = 2147483647

,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

,ON_FAILURE = CONTINUE

)

ALTER SERVER AUDIT Auditoria_Arquivo WITH (STATE = ON)

CREATE SERVER AUDIT SPECIFICATION [Criação de Banco de Dados]

FOR SERVER AUDIT [Auditoria_Arquivo]

ADD (DATABASE_CHANGE_GROUP)

WITH (STATE = ON)

USE [Testes]

CREATE DATABASE AUDIT SPECIFICATION [Audita_DML]

FOR SERVER AUDIT [Auditoria_Arquivo]

ADD (DELETE ON DATABASE::[Testes] BY [Usuario_Teste]),

ADD (INSERT ON SCHEMA::[dbo] BY [public]),

ADD (INSERT ON OBJECT::[dbo].[Clientes] BY [public])

WITH (STATE = ON)

Viewing the logs

Using simple queries, we can easily read the monitored data and work with this information:

-- Retorna as informações de um arquivo específico
SELECT * 
FROM Sys.fn_get_audit_file('C:\Auditoria_Arquivo_75804C51-45C9-423C-B570-B24202AD13D3_0_130899437085570000.sqlaudit',default,default)  

-- Retorna as informações de todos os arquivos
SELECT event_time,action_id,server_principal_name,statement,* 
FROM Sys.fn_get_audit_file('C:\*.sqlaudit',default,default)

-- Retorna as informações de um arquivo específico

SELECT *

FROM Sys.fn_get_audit_file('C:\Auditoria_Arquivo_75804C51-45C9-423C-B570-B24202AD13D3_0_130899437085570000.sqlaudit',default,default)

-- Retorna as informações de todos os arquivos

SELECT event_time,action_id,server_principal_name,statement,*

FROM Sys.fn_get_audit_file('C:\*.sqlaudit',default,default)

And the visualized data looks like this:

Thank you and see you next time!

sandro said:

December 3 from 2019 to 19: 03

Hello, seeing the audit logs (inserts, deletes, updates) only appears the @ variables and not the contents, as they arrive on the server. Any parameters?

- Dirceu Resende said:
  
  December 3 from 2019 to 19: 43
  
  Sandro, the audit does not capture the same content. The idea is just to audit what was done and by whom. To create a change history, you must implement audit trigger or cdc I quoted)
  
  - Sandro servino said:
    
    3 January 2020 to 19: 56
    
    A pity Dirceu, because knowing what has changed in terms of data is basic to any audit, but it was worth it. Another question, can you know if it was the user of the database that the application uses or a person with knowledge of this user and password who accessed a machine and ran the command via query analyzer?
    
. said:

8 November 2016 to 10: 03

Is it possible to display the IP or name of the client machine that performed the operation on the server?

- Dirceu Resende said:
  
  9 November 2016 to 09: 10
  
  Hello good day. This is not possible with SQL Audit, but you can get this information using SQL Profiler (Trace). Take a look at this post that I demonstrate how to use this feature: http://www.dirceuresende.com/blog/sql-server-como-auditar-erros-de-permissao-em-objetos-utilizando-o-sql-profiler-trace/
  
Luiz Vitor France Lima said:

11 from 2016 to 22 at 12: XNUMX

Excellent Post Dirceu! Very simple and practical. I followed the walkthrough and created an audit within minutes. Just complementing the post, some editions of SQL Server (Bussiness Intelligence, Standard, Web, and Express) do not support database level auditing, only at server level. Below is a link for reference. hug

https://www.logbinder.com/Content/Blog.aspx?p=d1f5efd3-d0a8-4c3e-82a5-6c8fc7e7f672

- Dirceu Resende said:
  
  9 November 2016 to 09: 11
  
  Thanks Luiz Vitor! Thanks for the info 🙂

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

Audit on SQL Server (Server Audit)