database auditing trigger - SQL Server Forums

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)

database auditing trigger

Author

Topic

jaynichols
Starting Member

18 Posts

Posted - 2008-10-08 : 08:57:05

Using the following trigger code, in some databases it causes SSIS jobs to fail with an ARITHABORT error message. Generally when deleting or creating a table which is when this trigger would be activated. Yet in other databases everything works ok. It also works ok when creating or dropping tables manually or from t-sql query.

/****** Object: DdlTrigger [SDB] Script Date: 10/07/2008 14:26:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [SDB] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
DECLARE @cmd NVARCHAR(1000)
DECLARE @posttime NVARCHAR(24)
DECLARE @spid NVARCHAR(6)
DECLARE @loginname NVARCHAR(100)
DECLARE @hostname NVARCHAR(100)
SET @data = EVENTDATA()
SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(1000)')
SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')
SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',
'NVARCHAR(100)')
SET @hostname = HOST_NAME()
INSERT INTO AdminAudit.dbo.SDB_AuditLog(Command, PostTime,HostName,LoginName)
VALUES(@cmd, @posttime, @hostname, @loginname)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [SDB] ON DATABASE

Dirt biking forever!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-08 : 09:00:39

So Is this information for future need?

Carat
Yak Posting Veteran

92 Posts

Posted - 2008-10-08 : 09:06:42

The user executing the SSIS package must have the permission to write in you logging table.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-10-08 : 09:13:05

Why are you creating and dropping tables in your SSIS package? Not a good plan.

If it is not practically useful, then it is practically useless.

jaynichols
Starting Member

18 Posts

Posted - 2008-10-08 : 10:59:44

Thank you Carat! That is the key. The db is owned by an ex-employee that does not have a login to the db the records are being written to. If they give points in this forum, you deserve many.

Sodeep. Not sure I understand. If you are talking about auditing the creation and removal of objects in the db, a DBA would be interested in these actions, don't you think?

Blindman. Yeah, I know. I've inheritied this operation just within the past 4 months. All this stuff was written in SQL2000 and converted to SQL2005 by people that were not that smart about how to do things in SQL. But it is not a good business practice to rewrite code that works, even if it is not efficient unless it impacts the operation of the company. After all, every line of code cost the company money so working programs represent an investment.

Dirt biking forever!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-10-08 : 13:16:41

Every hour of admin time costs the company money, so bad design and poor coding represent waste.

If it is not practically useful, then it is practically useless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources