Tag Archives: audit trail

Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required to audit the data, means, insert / update / delete should be tracked on some important table. There are many ways you can capture these information (Audit Trail), I have written quite a few articles on this subject too but all those needs additional code to be written. Fortunately my client is using SQL Server 2008 R2 version so I don’t even need to write down any specific code to capture changed data as there is facility of CDC (Change Data capture) in SQL Server 2008.
Even before we look at CDC in details, make sure you have your SQL Server Agent is running as CDC in SQL Server 2008 will use SQL Server Agent to make audit trail for you. If your SQL Server Agent is not running at the moment, start it from control Panel->Administrative Tools-> Services. You can find “SQL Server Agent (YourInstanceName)”, just start this service and then you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC in SQLHub database
–this will create “cdc” schema in SQLhub database too
–along with “cdc” schema, it will create some system table
–in “cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDC for our table created above.
–when you will enable cdc for ChangeDataCapture table
–it will create two job under SQL Server Agent
–which will read data from transaction whenever you will make any change in data
–and stores it in CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you have orginal table, named “ChangeDataCapture”
–another table to keep all changes “ChangeDataCapture”,
–which has been created in step above
select * fromChangeDataCapture
–following table will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let us make some DML operation in ChangeDataCapture table and
–observe how does it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 represent INSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of UPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 represent value before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect of DELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the data in both tables
select * fromChangeDataCapture
–in cdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 represent DELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Article on Audit facility in SQL Server 2008 in Experts-Exchange

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.

In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.

Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Auditing in SQL Server 2008 white paper.

A key part of any data security strategy is the ability to track who has accessed, or attempted to access, your data. This provides the ability to detect unauthorized access attempts or, if necessary, to piece together the actions of malicious insiders who misused their legitimate access. Furthermore, a rich and robust tracking capability can provide oversight of sensitive configuration changes made by administrators.
Such considerations are ever more relevant in today’s information economy. Data is collected, stored, used, and misused at an ever increasing rate. Governments and private sector organizations around the world are responding to this by establishing various compliance regimes to improve the stewardship of data by those who hold it. A few of the most widely known examples include:
  • European Union Data Protection Directive, a strict data protection policy with implications across the EU and the global economy.
  • HIPAA, or Health Insurance Portability and Accountability Act, part of United States law
  • Sarbanes-Oxley, part of United States law governing corporations.
  • Payment Card Industry Data Security Standard, mandated by major credit card companies, with worldwide implications.
These formal regulations affect organizations of all sizes, in all industries, around the world. They place significant pressure on organizations to ensure their IT platforms and practices are compliant. And ultimately, these requirements land at the feet of the DBAs, developers, and IT professionals who manage the data.
It is important that a data management platform provide the means to meet these requirements, and do so efficiently. To address these needs, SQL Server 2008 introduces a rich and deeply integrated auditing capability that offers major improvements over previous versions of the Microsoft® SQL Server® database software.
This paper will review the new audit features of SQL Server 2008, compare them to past versions, and walk through some implementation examples.
You can read full while paper by clicking here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Audit in SQL Server 2008

Audit is really one of the interesting, useful yet difficult to maintain topic in history of SQL Server. I have already written few articles on how to maintain Audit trail in SQL Server. You can refer those articles from the below given links, once you will go through those articles and then read ahead this one, you will come to know how much it is easy in SQL Server 2008.

Earlier people had very few options for auditing in SQL Server but in latest technology, you are having quite a good power on auditing stuff. Since this is one of the big topics in SQL Server, it is not possible to cover it completely here, though I will try to give you one detailed example herewith below.

First of all when you want to use power of Audit in SQL Server 2008, you have to create AUDIT object. AUDIT object is nothing more than just a container of Audit specification on Server level as well as on database level. You can store log in Application Event Log, Security Event Log and Filesystem.

I am going to create one Audit object which will act as a container of one of the database level audit specification which will keep an eye on one Schema for any DML statement executed on that schema.

I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.

–Select MASTER Database
USE master
GO
–create SERVER Audit
CREATE SERVER AUDIT [FirstAudit]
TO FILE
(
      FILEPATH=N’D:\AUDIT\’
      ,MAXSIZE=10 MB
      ,MAX_ROLLOVER_FILES=100
      ,RESERVE_DISK_SPACE=ON
)
WITH
(
      QUEUE_DELAY=1000
      ,ON_FAILURE=SHUTDOWN
      ,AUDIT_GUID=‘2EB5EF64-1B15-4AFF-B248-6F39D423E2E7’
)
GO
–Alter server audit object for
–making it enable
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO
–creating one test database,
–if it is exists, droping it first
if exists(SELECT 1 FROM SYS.DATABASES WHERE name=‘AuditTest’)
BEGIN
      USE master
      drop database AuditTest
END
CREATE DATABASE AuditTest
GO
–select newly created database
USE AuditTest
GO
–create two schema AUD1 and AUD2
CREATE SCHEMA Aud1
GO
CREATE SCHEMA Aud2
GO
–create two table
–one with each schema
if OBJECT_ID(‘Aud1.Table1’) is not null drop table Aud1.Table1
CREATE TABLE Aud1.Table1 (id int)
GO
if OBJECT_ID(‘Aud2.Table1’) is not null drop table Aud2.Table1
CREATE TABLE Aud2.Table1 (id int)
GO
–create database level AUDIT SPECIFICATION
–for our server audit created above
–which will keep watch on schema AUD1
–for DML statements but won’t watch for AUD2 schema
CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]
FOR SERVER AUDIT [FirstAudit]
ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])
WITH (STATE=ON)
GO
–making some DML actions in both table
–of both schema
INSERT INTO Aud1.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
INSERT INTO Aud2.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
UPDATE Aud1.Table1 set id=6 where ID=5
GO
UPDATE Aud2.Table1 set id=6 where ID=5
GO
–looking at what we have received in our audit file.
–you will know that you got data for AUD1 schema only
SELECT * FROM sys.fn_get_audit_file(‘D:\Audit\*’,DEFAULT,DEFAULT)
go
You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Keep log of Update in same table with Instead Of Trigger in SQL Server 2005

I wrote few articles on audit trails and would prefer to keep log in separate table even I see many times how to keep update track records in same table so I thought to create one example and share it with my readers.

CREATE TABLE STATSofTable

(

Status INT NOT NULL,

LOGS VARCHAR(MAX)

)

–insert into stats table

INSERT INTO STATSofTable (status,LOGS)

SELECT 1,‘TEST’ UNION ALL

SELECT 2,‘TEST’ UNION ALL

SELECT 3,‘TEST’

–CREATE TRIGGER TO UPDATE LOG AUTOMATICALLY

CREATE TRIGGER AuditTrailOnSTATSofTable ON STATSofTable

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

BEGIN

IF UPDATE(Status)

BEGIN

DECLARE @OldValue VARCHAR(1)

DECLARE @NewValue VARCHAR(1)

DECLARE @OldLog VARCHAR(max)

DECLARE @NewLog VARCHAR(max)

SELECT @OldValue = Status FROM Deleted

SELECT @NewValue = Status FROM INSERTED

SELECT @OldLog= LOGS from Deleted

SET @NewLog=@OldLog + ‘ STATUS CHANGE FROM ‘ + @OldValue + ‘ TO ‘+ @NewValue + ‘ ON ‘ + CONVERT(VARCHAR(50), getdate())

UPDATE STATS SET Status=@NewValue, LOGS=@NewLog WHERE Status=@OldValue

print @oldvalue

print @newvalue

print @OldLog

END

END

–UPDATE STATUS

update STATSofTable set status=4 where status =1

update STATSofTable set status=1 where status =4

select * from STATSofTable

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Auditing Trail with Trigger in SQL Server 2005

If you are new to this concept in database world than you might think what is Auditing Trail? The answer is pretty much easy and simple, if you want to increase the data-integrity and wants to have full details about insertion, edition and deletion of your data; Audit Trail is the concept you have to adopt.

Since this is a very huge topic and not possible to cover complete topic in one or may be in few articles, I will give some basic details here and will write some more article with live situation as and when time permits.

You would like to keep track of total update and delete in records after its insert, you may want which front-end accessed your row for modification, which user has changed which records.

There are many methods popular to keep track of your records change like you can have duplicate table for each table along with some comment field sand keep the track and old value. You may wish to create one table which will have details off all tables and about every single transaction but In that case, maintenance of that table is very crucial.

Let us create one table and try to keep trail of database.

–demo table for AuditTrail

–Note: this is just for demo purpose, in live situation we may have very big table than
— on explained here. everybody has their own need and situation.
CREATE TABLE AuditTrail(
ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT def_AuditTrail_Id DEFAULT(NEWID()),
AuditDate DATETIME,
TableName VARCHAR(50) NOT NULL,

ColumnName VARCHAR(50) NOT NULL,

Description VARCHAR(50) NOT NULL,

UserName VARCHAR(50) NOT NULL,

OldValue VARCHAR(50) NOT NULL,

NewValue VARCHAR(50) NOT NULL

)


–Now we will create one more table for which we will keep the trail in above table
–You can keep Audit of Insert, Delete and Update everything in above table

–and can customize it as per your need

CREATE
TABLE AuditDemo
(

ID INT IDENTITY(1,1) NOT NULL,

Name
VARCHAR(10),

Company VARCHAR(10)


— have few records in above table

INSERT INTO AuditDemo
SELECT
‘Ritesh’,‘eChem’ UNION ALL

SELECT ‘Rajan’,‘Marwadi’

–LET us create fixed audit trail trigger
create
TRIGGER AUDITonAuditDemo
ON
AuditDemo
AFTER UPDATE

AS

IF
UPDATE(Name)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Name’,‘Update’,suser_sname(),DELETED.Name,INSERTED.Name

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END

IF UPDATE(company)
BEGIN

INSERT
AuditTrail

(
AuditDate,TableName,ColumnName,Description,UserName,OldValue,NewValue)

SELECT

GETDATE
(),‘AuditDemo’,‘Company’,‘Update’,suser_sname(),Deleted.Company,Inserted.Company

FROM
INSERTED

JOIN
DELETED

ON
INSERTED.ID=DELETED.ID

END


–let us update record in AuditDemo tabel

UPDATE AuditDemo SET Name=‘R.Shah’ WHERE ID=2

–LET US NOW CHECK WHETHER WE HAVE TRAILED THE RECORDS OR NOT

SELECT * FROM AuditTrail


–let use update and check once agian

UPDATE AuditDemo SET Company=‘Testing’

SELECT * FROM AuditTrail

Hope you have enjoyed!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah