question

way2sequel avatar image
way2sequel asked

DML trigger for auditing loginname,hostname,ipadress,programname an dml statements

Hi Everybody, I am trying to setup a DML trigger to capture Programname,Loginname,Ip adress,Hostname along with Insert Update delete statements that have been executed.I did pursue options like CDC,change tracking,evendata,database audit specification,fn_dblog() but none of them offered me to capture the details i wanted. i did try tweaking the following query without any luck. /* This trigger audit trails all changes made to a table. It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed. It will put out an error message if there is no primary key on the table You will need to change @TableName to match the table to be audit trailed */ –Set up the tables if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [dbo].[Audit] go create table Audit (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128),Ip varchar(250),Hostname varchar(500)) go if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [dbo].[trigtest] go create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10)) go alter table trigtest add constraint pk primary key (i, j) go create trigger tr_trigtest on trigtest for insert, update, delete as declare @bit int , @field int , @maxfield int , @char int , @fieldname varchar(128) , @TableName varchar(128) , @PKCols varchar(1000) , @sql varchar(2000), @UpdateDate varchar(21) , @UserName varchar(128) , @Type char(1) , @hostname varchar(500)=(SELECT HOST_NAME()), @ip VARCHAR(32) = ( SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID ), @PKSelect varchar(1000) select @TableName = ‘trigtest’ — date and user select @UserName = system_user , @UpdateDate = convert(varchar(8), getdate(), 112) + ‘ ‘ + convert(varchar(12), getdate(), 114) — Action if exists (select * from inserted) if exists (select * from deleted) select @Type = ‘U’ else select @Type = ‘I’ else select @Type = ‘D’ — get list of columns select * into #ins from inserted select * into #del from deleted — Get primary key columns for full outer join select @PKCols = coalesce(@PKCols + ‘ and’, ‘ on’) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = ‘PRIMARY KEY’ and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME — Get primary key select for insert select @PKSelect = coalesce(@PKSelect+’+’,”) + ””” from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = ‘PRIMARY KEY’ and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME if @PKCols is null begin raiserror(‘no PK on table %s’, 16, -1, @TableName) return end select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName while @field @field select @bit = (@field – 1 )% 8 + 1 select @bit = power(2,@bit – 1) select @char = ((@field – 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (‘I’,’D’) begin select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field select @sql = ‘insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName,Ip,Hostname)’ select @sql = @sql + ‘ select ”’ + @Type + ”” select @sql = @sql + ‘,”’ + @TableName + ”” select @sql = @sql + ‘,’ + @PKSelect select @sql = @sql + ‘,”’ + @fieldname + ”” select @sql = @sql + ‘,convert(varchar(1000),d.’ + @fieldname + ‘)’ select @sql = @sql + ‘,convert(varchar(1000),i.’ + @fieldname + ‘)’ select @sql = @sql + ‘,”’ + @UpdateDate + ”” select @sql = @sql + ‘,”’ + @UserName + ”” select @sql = @sql + ‘ from #ins i full outer join #del d’ select @sql = @sql + @PKCols select @sql = @sql + ‘,”’ + @ip + ”” select @sql = @sql + ‘,”’ + @hostname + ”” select @sql = @sql + ‘ where i.’ + @fieldname + ‘ d.’ + @fieldname select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is null and d.’ + @fieldname + ‘ is not null)’ select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is not null and d.’ + @fieldname + ‘ is null)’ exec (@sql) end end go insert trigtest select 1,1,’hi’, ‘bye’ insert trigtest select 2,2,’hi’, ‘bye’ insert trigtest select 3,3,’hi’, ‘bye’ update trigtest set s = ‘hibye’ where i 1 update trigtest set s = ‘bye’ where i = 1 update trigtest set s = ‘bye’ where i = 1 update trigtest set t = ‘hi’ where i = 1 select * from Audit select * from trigtest delete trigtest Any help would be highly appreciated. Thank you, BT
securityauditdml
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

way2sequel avatar image way2sequel commented ·
Hi Everybody, Any help will be highly appreciated. Thank you, BT
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
There are some problems with the code you've posted: 1. The `WHILE` statement is missing something. `WHILE @field @field` doesn't seem right, syntactically. 2. There have been some horrible substitutions - curly apostrophes (some of which have been doubled up) etc. I suspect this code has been Wordified... :-( There are a couple of tools on this site to help you ask questions with properly formatted code - one is the redgate code formatter, the other is the box that tells the system that a chunk of text is code. Please help us to help you, and repost the code as copied straight from SSMS.
0 Likes 0 ·
way2sequel avatar image way2sequel commented ·
Thanks a bunch for the reply Thomas! Highly appreciate it. I am sorry for posting the wordified code :( i actually got that code from some other blog and copied it to here directly and i dont find that page any more :( ,i am using this script to achieve my task,with the tests did so far i think i am getting there but i am not able to capture all INSERT,DELETE,UPDATES and the count doesn't increment after each DML.I am unable to post the code here since due to character limit,i have posted below as an answer.Thank you in advance.
0 Likes 0 ·

1 Answer

·
way2sequel avatar image
way2sequel answered
Thanks a bunch for the reply Thomas! Highly appreciate it. I am sorry for posting the wordified code :( i actually got that code from some other blog and copied it to here directly and i dont find that page any more :( ,i am using this script to achieve my task,with the tests did so far i think i am getting there but i am not able to capture all INSERT,DELETE,UPDATES and the count doesn't increment after each DML. CREATE TRIGGER tr_log_DML_cmds ON Table FOR DELETE, UPDATE AS BEGIN SET nocount ON DECLARE @shouldlog BIT, @insertcount BIGINT, @deletecount BIGINT SELECT @shouldlog = 1, @insertcount = (SELECT Count(*) FROM inserted), @deletecount = (SELECT Count(*) FROM deleted) -- if no rows are changed, do not log IF @insertcount < 1 AND @deletecount < 1 BEGIN SELECT @shouldlog = 0 END -- ... other checks whether to log or not IF @shouldlog = 1 BEGIN -- prepare variable to capture last command DECLARE @buffer TABLE ( eventtype NVARCHAR(30), parameters INT, eventinfo NVARCHAR(4000) ) -- use DBCC INPUTBUFFER to capture last command -- unfortunately only the first 255 characters are captured INSERT @buffer EXEC Sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS' DECLARE @lastcommand VARCHAR(max) SELECT @lastcommand = eventinfo FROM @buffer -- insert into audit table INSERT INTO [MDW2].dbo.[DML_AUDIT2] (eventdate, tablename, hostname, appname, insertcount, deletecount, command, [user_name], [suser_name], [current_user], [system_user], [session_user], [user], [application_name], [occurance_date]) VALUES ( Getdate(), 'Table', Host_name(), App_name(), @insertcount, @deletecount, @lastcommand, User_name(), Suser_name(), CURRENT_USER, SYSTEM_USER, SESSION_USER, USER, App_name(), Getdate() ) END END Many Thanks, BT
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.