November 18, 2011 at 12:43 pm
Hello,
I am a very db knowledgeable developer but not a db developer so forgive me for the simple question. Our web app is changing data and we can't seem to identify why.
This only happens to a few people and we have not yet figured out how to reproduce in test...thus finding and fixing is very difficult.
Quickly what happens is the user logs in and changes some features. In the database we change these values to 0 or 1 in a bit field. The user logs off, logs back in and as is well. Indeed it seems that it only happens after a long period of time. So the user will stay logged out for 4 hours, log back in and the settings have changed???
We are unsure if this happens on it's own or during login but only after a "cooling off period". So I was looking for something that I could configure to watch table X and better yet watch rows 1-5 in table X. From there I would then be able to identify a job, or a stored procedure etc. to start figuring this out.
Thank You
JB
November 18, 2011 at 1:16 pm
If you have access to BOL (Books On Line - the SQL server help file) you may want to investigate the subject "SQL Server Audit Action Groups and Actions ". It seems to be an applicable method of tracing those changes you have described.
November 18, 2011 at 4:32 pm
BOL??? Who needs that when you have Google? Thank You for pointing me in the right direction. Well so much for that idea. Only available SQL 2008 and this is 2005.
JB
November 19, 2011 at 2:10 am
Hi,
You could setup a trigger on the table to get details of when the row was updated and store the details in an audit table
There are many different approaches to doing this, but if you need some help, let me know and i'll post some code which is easy to implement.
if your application is on a domain, then the trigger will be able to pickup which user made the changes - if it's not on a domain, then its likely that the application will be running under a general account - something like IIS_USER on windows I think. Like you say - it could also be something in the application that is changing this - if you have a trigger on the table, at least you can see exactly when it's happening which might give you a better clue.
November 19, 2011 at 11:10 am
Well I've written triggers before but not for auditing. Typically just for business rules purposes. So my question is would I have a parameter available telling me stored procedure XYZ changed it or maintenance job XYZ etc. I know it is getting changed I can see that in that SSMS what I need is a clue who. If you have code to help that would be helpful or even just some trigger parms / values to read up on.
TIA
JB
November 19, 2011 at 11:50 am
Hi,
I'll give you sthe stuff I use - this will tell you at least who and when the data was updated.
Step 1 create the table to store the updates.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tbl_UpdateMonitor](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](255) NULL,
[ChangeDate] [datetime] NULL,
[OldValues] [xml] NULL,
[newValues] [xml] NULL,
[Username] [varchar](50) NULL,
CONSTRAINT [PK_Tbl_UpdateMonitor] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Tbl_UpdateMonitor] ADD CONSTRAINT [DF_Tbl_UpdateMonitor_ChangeDate] DEFAULT (getdate()) FOR [ChangeDate]
GO
Step 2 Create a stored procedure to inset details of the updates into the audit table.
CREATE PROCEDURE [dbo].[u_proc_UpdateMonitor]
@TableName sysname,
@Deleted xml,
@Inserted XML
AS
BEGIN
if @Deleted is not null or @Inserted is not NULL
BEGIN
insert into Tbl_UpdateMonitor (TableName, OldValues, NewValues, UserName)
values (@TableName, @Deleted, @Inserted, SYSTEM_USER)
END
END
(make sure that public has execute on this)
Step 3 Create the trigger on the table - just replace TABLENAME with the name of the table you want to monitor.
CREATE trigger [dbo].[Tbl_TABLENAME_T_Log] on [dbo].[Tbl_TABLENAME]
for insert, update, delete
as
declare @i xml, @d xml, @t VARCHAR(255)
SET @T = (select object_name(id) from sysobjects where instrig = @@procid)
set @i = (SELECT * from inserted for xml auto)
set @d = (select * from deleted for xml auto)
exec u_proc_UpdateMonitor @T,@D, @I
GO
Hope this is of some help - Good luck!
December 8, 2011 at 9:14 am
craigB-921010,
I don't see where on this forum I can mark as closed nor give credit other than another post. It took awhile to roll through QA and prod cycles. The script didn't point a finger (not sure it could have) but it did provide historical data and I was able to notice a pattern in the data change. From there I narrowed it down to one stored procedure and then a couple lines of code and not it is fixed.
Thank You Very Much!
JB
December 8, 2011 at 5:27 pm
glad to help. 🙂
In case you need to reference this in future, I have done some improvements to the update trigger recently. This means the updates are stored as propper structured XML - the benefit of doing this, is you can use it as a datasource in an application for viewing the change log - I have also written that too - it loads the changes whiich you can filter by user, table, date etc, and then hightlights which fields have been modified. if you think it'd be useful, give me a shout
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[Tbl_TABLENAME_T_Log] on [dbo].[Tbl_TABLENAME]
for insert, update, delete
as
declare @i xml, @d xml, @t VARCHAR(255)
SET @T = (select object_name(id) from sysobjects where instrig = @@procid) --this is the current table
SET @i = (SELECT * FROM inserted for xml PATH('fields'), ROOT('newvalues'))
SET @d = (SELECT * FROM deleted for xml PATH('fields'), ROOT('oldvalues'))
exec u_proc_UpdateMonitor @T,@D, @I
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply