SQL Server Audit in SQL Server 2008 – Part 2

In part 1 of this article series, we illustrated how to use SQL Server Audit to monitor server-level actions and write audit results to the Windows Application Log. In this article, we will show you how to monitor database-level actions and write audit results to files.


A Database Audit Specification collects database-level audit action groups or individual actions. In our first example, we would like to monitor database principals and their permission changes. To monitor database principals, we need to include the audit action groups listed below.




  • DATABASE_ROLE_MEMBER_CHANGE_GROUP
    This group records actions that adds or removes a principal from a database role.



  • DATABASE_PRINCIPAL_CHANGE_GROUP
    This group records actions that create, alter or drop a principal from a database.



  • DATABASE_PERMISSION_CHANGE_GROUP
    This group records actions that GRANT, REVOKE, or DENY permissions for a statement.



  • DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
    This group records actions that GRANT, REVOKE, or DENY permissions for a database object.



  • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
    This group records actions that GRANT, REVOKE, or DENY permissions for a schema object.


Let’s create a SQL Server Audit object and a Database Audit Specification Object on the default SQL Server instance on the computer PowerPC. The audit object writes to binary files under the directory C:\SQLAudit.

USE master
IF EXISTS (SELECT * FROM sys.server_audits
WHERE name = N’MyServerAudit’)
BEGIN
ALTER SERVER AUDIT MyServerAudit WITH (STATE = OFF)
DROP SERVER AUDIT [MyServerAudit]
END
GO
/* Create a SQL Server Audit Object that writes audit results to the C:\SQLAudit directory every one second.
If the write fails, the instance continues running without stopping.
Before creating the audit, make sure the directory C:\SQLAudit exists.
The audit file name is generated based on the audit name and audit GUID.
*/
CREATE SERVER AUDIT MyServerAudit
TO FILE ( FILEPATH =’C:\SQLAudit\’ )
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
/* By default, the audit is created in the disabled state.
We need to enable it before using it to record actions. */
ALTER SERVER AUDIT MyServerAudit
WITH (STATE = ON);
USE AdventureWorks
IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N’MyDatabaseAuditSpecification’)
BEGIN
ALTER DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpecification WITH (STATE = OFF)
DROP DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpecification
END
GO
/* Create a Database Audit Specification object for the server audit.
This object includes five audit action groups related to database principal changes. */
CREATE DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpecification
FOR SERVER AUDIT MyServerAudit
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP);
/* By default, the audit specification is created in the disabled state.
We need to enable it before using it to record actions. */
ALTER DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpecification
WITH (STATE = ON);

After we run this script, an audit file named MyServerAudit_CF0E46C1-F6D0-41CC-9D3D-5F34BEE011DE_0_128562033925420000.sqlaudit is created under the C:\SQLAudit directory.


Let’s test our database audit by running the following statements against the SQL Server.

CREATE LOGIN testLogin WITH PASSWORD = ‘Welcome123’
USE AdventureWorks
Create USER testLogin FOR LOGIN testLogin
EXEC sp_addrolemember ‘db_datareader’, ‘testLogin’
GRANT CREATE TABLE TO testLogin
GRANT INSERT ON HumanResources.Employee TO testLogin
GRANT UPDATE ON Schema::Person TO testLogin
DROP USER testLogin
DROP LOGIN testLogin

To confirm the actions have been recorded, read from the audit file using the sys.fn_get_audit_file function.

SELECT event_time, action_id, succeeded, session_id, session_server_principal_name,
target_server_principal_name, target_database_principal_name, database_name,
database_principal_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file (‘C:\SQLAudit\MyServerAudit_*.sqlaudit’,default,default);
GO

Each audit record has 28 columns. We only include a subset of columns here for analysis. You might need to examine other columns in different scenarios. For example, the is_column_permission column indicates that column permissions are being granted, revoked or denied. You would like to examine it for granular level permissions.




As we can see above, the first row with an action id AUSC indicates when an audit specification was created for the audit object. The second row corresponds to the user creation for the server login testLogin in the AdventureWorks database. The third row corresponds to the addition of the testLogin into the db_datareader role. The fourth, fifth and sixth rows correspond to granting permissions for the CREATE TABLE statement, INSERT on the HumanResources.Employee table, and UPDATE on the Person schema. The last action corresponds to dropping testLogin from the AdventureWorks database. All our database-level actions have been recorded.


A Database Audit Specification can also collect individual actions. For example, to audit the UPDATE action on the HumanResources.Employee table by dbo, we can create another audit specification.

CREATE DATABASE AUDIT SPECIFICATION UPDATE_EMPLOYEE_Specification
FOR SERVER AUDIT MyServerAudit
ADD (UPDATE
ON HumanResources.Employee
BY dbo);

ALTER DATABASE AUDIT SPECIFICATION UPDATE_EMPLOYEE_Specification
WITH (STATE = ON);


Let’s update a row in the HumanResources.Employee table as the dbo user.

UPDATE HumanResources.Employee
SET MaritalStatus=’M’
where EmployeeID = 2

Read from the audit files by running the same SQL statement we ran above.

SELECT event_time, action_id, succeeded, session_id, session_server_principal_name,
target_server_principal_name, target_database_principal_name, database_name,
database_principal_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file (‘C:\SQLAudit\MyServerAudit_*.sqlaudit’,default,default);
GO

Two more audit records are returned.

2008-05-26 17:28:38.211,AUSC,1,7,,,,,,,,
2008-05-26 18:27:52.803,UP ,1,56,POWERDOMAIN\PowerUser,,,AdventureWorks,dbo,HumanResources,Employee,
UPDATE [HumanResources].[Employee] set [MaritalStatus] = @1 WHERE [EmployeeID]=@2

The first row with an action id AUSC indicates another audit specification object was created for the audit object. In the second row, the action id UP indicates an update operation was performed on the HumanResources.Employee table by the login POWERDOMAIN\PowerUser. The SQL statement is included in the statement column. However, notice that the values of the MaritalStatus and EmpolyeeID parameters are not shown in the statement because SQL Server Audit is meant to audit actions, not data changes. If you would like to track data changes on the table, you should use Change Tracking.


Conclusion


In this article, we illustrated how to audit database-level actions using SQL Server Audit.


» See All Articles by Columnist Yan Pan

Yan Pan
Yan Pan
Yan Pan (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, OCA Oracle 10g) is a Senior DBA for a leading global financial services firm, where her daily duties include administering hundreds of SQL Server and Oracle servers of every possible version, working with business units on software development, troubleshooting database issues, and tuning database performance. She has written a Wrox book titled “Microsoft SQL Server 2008 Administration with Windows PowerShell” with MAK who is also a columnist for DatabaseJournal.com. You can check out the book at many book stores, such as Barnes & Noble, Borders. Previously, Yan worked as a SQL Server DBA and a .NET developer at Data Based Ads, Inc., and developed two .NET Web applications. Before that, she worked at AT&T Research Labs. She designed OLAP cubes with SQL Server Analysis Services and developed PivotTable reports for regional managers. She has master’s degrees in Computer Science and Physics.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles