To implement SQL Server audits, you first need to create a SQL Server Audit object. This object collects server and/or database-level actions and groups of actions that you want to monitor. It is created at the SQL Server instance level. You can have multiple SQL Server Audit objects per SQL Server instance. In the SQL Server Audit object, you specify the location for the output of the audit results. The results can be written to a binary file, the Windows Application Log, or the Windows Security Log. The file or the log is the Target component and the consumer of the associated extended events. Then you create Server or Database Audit Specification objects that belong to the SQL Server audit object. A Server Audit Specification collects server-level actions grouped in server audit action groups. It cannot collect individual actions.
However, a Database Audit Specification collects both database-level audit action groups and individual actions.
In Part I of this series, we will focus on the server level events. Server audit action groups record logon and logoff operations, server principal changes, server and database role membership changes, database maintenance operations, server object changes, database object changes, server and database statement and object permission changes. Any database related action groups are collected on all the databases on the server instance.
For example, DATABASE_ROLE_MEMBER_CHANGE_GROUP in a Server Audit Specification collects any actions that add or remove a login from a database role in any database on the server. If you want to audit only one database, you will need to define a Database Audit Specification in that specific database.
As part of the auditing requirements, we would like to monitor server logins and their server-level permission changes. To monitor login additions and deletions, we can include the SERVER_PRINCIPAL_CHANGE_GROUP audit action group in our server audit specification. Server-level permissions can be changed by issuing GRANT, REVOKE, or DENY, or through assigning server role memberships. Therefore, we can include the SERVER_PERMISSION_CHANGE_GROUP and SERVER_ROLE_MEMBER_CHANGE_GROUP audit action groups.
In this script, we write the audit to the Application Log.
/* Create a SQL Server Audit Object that writes the audit results to the Windows Application Log every one second.
If the write fails, the instance continues running without stopping. */
CREATE SERVER AUDIT MyServerAudit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
/* Create a Server Audit Specification object for the server audit.
This object include three audit action groups related to server principal changes. */
CREATE SERVER AUDIT SPECIFICATION MyServerAuditSpecification
FOR SERVER AUDIT MyServerAudit
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);
/* By default, both the audit and audit specification are created in the disabled state.
We need to enable them before using them to record actions. */
ALTER SERVER AUDIT SPECIFICATION MyServerAuditSpecification
WITH (STATE = ON);
ALTER SERVER AUDIT MyServerAudit
WITH (STATE = ON);
SELECT * FROM sys.server_audit_specifications
To confirm that an extended event session has been created, we can select from the extended events dynamic management views.select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address
As we can see above, an extended event session called MyServerAudit$A was created. It includes the audit_event from the SecAdudit package. The audit is written to Application Event log asynchronously.
Let’s test this script by adding a login called testlogin. Grant the View Server State permission to the login, and add it to the dbcreator role.CREATE LOGIN testLogin WITH PASSWORD = 'Welcome123'
GRANT VIEW SERVER STATE TO testLogin
EXEC sp_addsrvrolemember 'testLogin', 'dbcreator'
DROP LOGIN testLogin
We can examine the Application Log by running the Windows Powershell command below.
Get-EventLog -logname "application" -newest 4 where {$_.Source -eq 'MSSQLSERVER' } select EntryType, EventID, Message, TimeGenerated format-list
