overhead of auditing using DDL triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

overhead of auditing using DDL triggers

Author

Topic

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2009-10-09 : 13:19:46

Hello!

I would like to implement SQL Server auditing using DDL triggers (capture DML, permission, etc. audit log). I was wondering how much overhead this would add. I am planning to implement DML trigger for each user defined database and on server level. Our environment is high transactional OLTP SQL Server 2005 SP2.

Comments are appreciated,
Igor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-09 : 13:29:34

sorry didnt understand the meaning of 'implement DML trigger for each user defined database and on server level'. did you mean some thing like this?

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/28/using-ddl-triggers-to-dynamically-create-dml-triggers.aspx

imarchenko
Yak Posting Veteran

57 Posts

Posted - 2009-10-12 : 19:41:56

This is what I am trying to do:
...
create trigger trg_audit_server_changes
on ALL server
for CREATE_DATABASE,ALTER_DATABASE, DROP_DATABASE,
CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN,
GRANT_SERVER, DENY_SERVER,REVOKE_SERVER,
ALTER_AUTHORIZATION_SERVER

as

set nocount on

declare @data xml
set @data = EVENTDATA()

insert into DBA.dbo.AuditChangeLog
(databasename,
eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
'Server',
--@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)

GO
/*********************************************************/
--generate audit trigger for each user defined database
DECLARE @strSQL VARCHAR(MAX)
SELECT @strSQL='

IF EXISTS ( SELECT *
FROM sys.triggers
WHERE name = ''trg_audit_database_changes'' )
DROP TRIGGER trg_audit_database_changes ON database
GO
CREATE TRIGGER TRG_AUDIT_DATABASE_CHANGES
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

SET NOCOUNT ON

declare @data xml
set @data = EVENTDATA()

INSERT INTO DBA.dbo.AuditChangeLog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''varchar(50)''),
@data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''varchar(256)''),
@data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''varchar(25)''),
@data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''varchar(max)''),
@data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''varchar(256)'')
)
GO
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources