Post reply

Log user activity, who is accessing SQL server through an application.

  • March 24, 2005 at 9:25 am

    #362248

    Hello,

    I would like to know if there is a way to track data changes. I mean which user has updated what data. The problem being, users access data in SQL through a third party application. This application doesn't have any user authentication. Thats the downside, the bright side is, all the users use NT authentication in order to access the data. So I can see the users currently accessing the data.

    So is there a way to track changes & if so, can you give an example.

    Thanks to all.

  • March 24, 2005 at 10:05 am

    #548801

    You have two possible methods.

    If you are trying to track on a table you can set triggers on the table and write it to a narc table

    SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER LoginTracking 
    ON TCL.dbo.SECUSER
FOR UPDATE
    AS IF UPDATE (USERCOUNT)
BEGIN
    INSERT INTO Narc_UserLogins (WkStID, Process_ID, 
    USERDESC, USERNAME, CHANGETIME, Curr_Count, 
    New_Count)
    SELECT HOST_NAME() as WkStID, @@SPID as Process_ID,
    SECUSER.USERDESC, SECUSER.USERNAME,
    GETDATE() AS CHANGETIME, deleted.USERCOUNT, 
    SECUSER.USERCOUNT
    FROM SECUSER, DELETED
    WHERE SECUSER.USERNAME = DELETED.USERNAME
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

    The other is to setup a trace on the database to gather all changes. That is a little more complicated. You can use the profiler to build that.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • March 24, 2005 at 10:12 am

    #548804

    Another mechanism is to use log exploring software like what Lumigent or ApexSQL sells. There's also Entegra from Lumigent.

    K. Brian Kelley
    @kbriankelley

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply