Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 3, 2010

Database Level Auditing with Microsoft SQL Server 2008

By Muthusamy Anantha Kumar aka The MAK



MAK shows us how easy it is to create a database level audit, test the audit and retrieve the audit records in Microsoft SQL Server 2008.

Creating a Server Level Audit the Easy Way," illustrated how to create a server level audit and retrieve the audit records. The next logical step is to create a database level audit. However, before creating the database level audit we need to decide where the target is going to be. Let's choose a FILE as our target for the Database level audit.

Step 1: Create a Folder

Create a folder, C:\SQLAudit, as shown in Fig 1.0.

choose a FILE as target for the database level audit
Fig 1.0 - choose a FILE as target for the database level audit



Step2: Create the Server Audit

Create the Server Audit Object using the following command.

CREATE SERVER AUDIT Compliance_Database_Audit 
TO FILE (FILEPATH='C:\SQLAudit\', MAXSIZE = 5MB, 
MAX_ROLLOVER_FILES = 100, RESERVE_DISK_SPACE = ON)

Note: Compliance_Database_Audit is the name of the Server Audit. This command allocates 5MB of disk space for this audit. When the 5MB file is filled up, it automatically rolls over to another file up to a maximum number of 100 Files. When the audit restarts the next time, it evaluates the number of files and delete any older files.



Step 3: Enable the Server Audit

By default, the server audit is disabled. Enable the server audit using the following command.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = ON)

Note: Once the audit starts, the OS allocates 5MB disk space for the audit file. Refer to Fig 1.1.



Enable the Server Audit
Fig 1.1 - Enable the Server Audit

Step 4: Add the Database Audit specification

The database audit specification is the list of action groups that the Server Audit will audit. Let's add a couple of action groups when creating the Audit specification.

CREATE DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec 
	FOR SERVER AUDIT Compliance_Database_Audit  ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),  
	ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), 
	ADD (DATABASE_PERMISSION_CHANGE_GROUP), 
	ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP) WITH (STATE = OFF)

Note: Compliance_Database_Audit_Spec is the name of the Server Audit specification

Step 5: Update the Database Audit specification

If you want to add action groups to the existing database audit specification, you could use the following statement.

ALTER  DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec 
	FOR SERVER AUDIT Compliance_Database_Audit  
	ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

Note: You can skip step 4 if you have already listed all of the action groups in Step 4.

Step 6: Enable Server Audit specification

Use the following command to enable Audit specification.

ALTER DATABASE AUDIT SPECIFICATION Compliance_Database_Audit_Spec
	FOR SERVER AUDIT Compliance_Database_Audit WITH (STATE = ON)

Step 7: Read Audit data

Execute the following command to read the AUDIT data from the binary file. Refer to Figure 1.2.

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\*Compliance_Database_Audit_*.sqlaudit',default,default);

Read Audit data
Fig 1.2 - Read Audit data

Step 8: Test Audit

We have the following action group added to the Audit specification.

DATABASE_ROLE_MEMBER_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP
DATABASE_PERMISSION_CHANGE_GROUP
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP

Let's test the action group by executing the following command.

use master
go
/****** Object:  Login [MyLogin]    Script Date: 01/10/2010 03:47:53 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin')
DROP LOGIN [MyLogin]
GO
Create Login MyLogin with password = 'P@ssWord'
go
/****** Object:  Login [MyLogin2]    Script Date: 01/10/2010 03:48:10 ******/
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MyLogin2')
DROP LOGIN [MyLogin2]
GO
Create Login MyLogin2 with password = 'P@ssWord'
go
USE [master]
GO
/****** Object:  Database [MyDB]    Script Date: 01/10/2010 03:48:19 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO
Create database MyDB
go
use MyDB
go
USE [MyDB]
GO

/****** Object:  Table [dbo].[mytable]    Script Date: 01/10/2010 03:48:41 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO

create table mytable (id int)
go
USE [MyDB]
GO
/****** Object:  User [MyLogin]    Script Date: 01/10/2010 03:48:56 ******/
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin')
DROP USER [MyLogin]
GO
create user MyLogin for Login MyLogin
go
USE [MyDB]
GO

/****** Object:  User [MyLogin2]    Script Date: 01/10/2010 03:49:13 ******/
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyLogin2')
DROP USER [MyLogin2]
GO

create user MyLogin2 for Login MyLogin2
go
USE [MyDB]
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin'
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_droprolemember N'db_owner', N'MyLogin2'
GO
EXEC sp_changedbowner N'MyLogin2'
GO

Now let's see if these actions have been logged to the binary file under c:\SQLAudit by executing the followoing command. Refer to Fig 1.3.

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\*Compliance_Database_Audit_*.sqlaudit',default,default);

see if these actions have been logged to the binary file
Fig 1.3 - see if these actions have been logged to the binary file

Step 9: Check the Audit metadata

Use the following commands to view metadata information on the server audit and audit specification.

SELECT * FROM sys.server_audits
SELECT * FROM sys.Database_audit_specifications

view metadata information on the server audit and audit specification
Fig 1.4 - view metadata information on the server audit and audit specification

You also check the extended events that are created by SQL server related to this audit using the following Dynamic management view command. Refer to Fig 1.5.

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

check the extended events that are created by SQL server
Fig 1.5 - check the extended events that are created by SQL server

Step 10: Stop Server AUDIT

Server audit can be stopped using the ALTER SERVER AUDIT command a shown below.

ALTER SERVER AUDIT Compliance_Database_Audit with (STATE = OFF)

Note: When the audit is stopped, SQL Server releases the free space on the audit files to the Operating System. Refer to Fig 1.6

Stop Server AUDIT
Fig 1.6 - Stop Server AUDIT

Conclusion

This article has illustrated how to create a database level audit, test the audit and retrieve the audit records.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date