Friday, September 3, 2010

Introduction to Auditing in sql server versions !@!

I have talked in depth about how auditing is possible with sql server 2008.I shall now talk about how auditing happens in sql server 2000 and sql server 2005.

In sql server 2000 we can create DML triggers but not DDL triggers.
In sql server 2005 we can create DML and DDL triggers.With this version we can not only create triggers for specific events but a group of related events i.e. event groups.For example :

create trigger trig1
on database
for create_login,alter_login,drop_login
as
begin
---
--
end

create trigger trig1
on database
for DDL_LOGIN_EVENTS
as
begin
---
--
end

This DDL_LOGIN_EVENTS will include create_login,alter_login and drop_login.

In sql server 2008 ,auditing functionality has been tremendously increased with the advent of server audits,server audit specifications and database audit specifications.