Home > SQL Server 2008 > Auditing if someone runs the DBCC command apart from DBA team and DELETE issued against an important table in SQL Server 2008

Auditing if someone runs the DBCC command apart from DBA team and DELETE issued against an important table in SQL Server 2008


Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. Based on the information accumulated we would be able to track the changes to the database, access to the database etc. In this article we have audited if someone runs the DBCC command apart from DBA team and DELETEs issued against an important table.

While we are working with SQL Server 2008 auditing we need to keep four things in mind:

  1. SQL Server Audit
  2. Server Audit Specification (Events to capture on the Server Instance Level)
  3. Database Audit Specification (Events to capture on a specific database)
  4. Target (Where would be the events be logged)

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. When you define an audit, you specify the location for the output of the result. This is the audit destination. The audit is created in a disabled state, and does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope

Database Audit Specification

The Database Audit Specification object also belongs to a SQL Server Audit. You can create one database audit specification per SQL Server database per audit. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification.

 

Target

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log .Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records. Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and Network Service are part of this policy.

Step Need to follow:

  • Audit DBCC usage.
  • Audit DELETEs issued against a table.
Tasks Supporting information
1…..It is very easy to capture information about activities in the database through Audits. We can create an Audit and associate events to capture at the server and/or the database level. Start with an audit storing information in the Application Log. Within the Instance’s Security expand to Audits 

Right Mouse click and add an Audit
Name: Demo Audit
Audit Destination: Application Log
OK

Right Click the new Audit and ‘Enable’ it.

2…..Just below the Audit let us add a Server level audit based on the DBCC_Group so we can determine who is running any DBCC Command and which one. Add ‘New Server Audit Specification
Name: DBCC Usage
Audit: Demo Audit
Uncheck Enabled (Very Important with CTP 6)
Audit Action Type: DBCC_Group
OK 

Right Click the new Specification and ‘Enable….’ it.

3…..Additionally, add a Database Audit Specification to the AdventureWorks2008 to capture every delete attempted against Sales.SpecialOffer Add New Database Audit Specification
Name: Archive Deletes
Audit: Demo Audit
Uncheck Enabled (Very Important with CTP6)
Audit Action Type: DELETE/Production/TransacationHistoryArchive
Principal – choose ellipse and check all users.
OK 

Right Click the new Specification and ‘Enable…’ it.

4…..Let’s see what type of entries are created by first clearing our Procedure Cache and then by deleting five records of the Transaction History. Use the Audit.sql scrpt: 

DBCC FREEPROCCACHE

GO

USE AdventureWorks2008
GO

BEGIN TRAN

DELETE TOP(5) Production.TrasactionHitoryArchive

ROLLBACK

5….Open the Event Viewer and look at the results. Find the two latest Audit Success items and review for the DBCC call and the deletion of data out of a table.
Advertisements
Categories: SQL Server 2008
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: