none
How to find which login/user has modified the record in a table in SQL server 2008.

    Question

  • Hi,

    I would like to find which user has modified the record(if possible, particular record) in a table/table in SQL Server 2008 Enterprise Edition.

    The problem is, we are all using the common username(sa) and password...? Every one has rights to perform all commands? The only diff. was our system names.

    Is there any way to find out such things through sys tables/DMVs or through any method??

    Note: We don't have any triggers to record such record such things.

    Thanks in Advance.

    Mahesh

    Thursday, April 28, 2011 6:24 AM

Answers

  • If you are using a common sql login user for all connections, then it is not easy to find which user updated a record if you do not have an UpdatedBy column in your tables and populate this field from an application that sends login info to the SQL Server.

    Even in such a case, if the users can directly connect to SQL Server using sql login, then the UpdatedBy column will not be populated securely.

    Then you should search from Server events who connected from which client to SQL Server computer, then check the UpdatedDate if you have any, etc and that will not give you a certain username at the end.

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Marked as answer by KJian_ Wednesday, May 04, 2011 7:50 AM
    Thursday, April 28, 2011 6:33 AM
    Moderator

All replies

  • Hi,

    I think there is no other way,you need to use trigger to audit the changes record


    Thanks and regards, Rishabh
    Thursday, April 28, 2011 6:32 AM
  • If you are using a common sql login user for all connections, then it is not easy to find which user updated a record if you do not have an UpdatedBy column in your tables and populate this field from an application that sends login info to the SQL Server.

    Even in such a case, if the users can directly connect to SQL Server using sql login, then the UpdatedBy column will not be populated securely.

    Then you should search from Server events who connected from which client to SQL Server computer, then check the UpdatedDate if you have any, etc and that will not give you a certain username at the end.

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Marked as answer by KJian_ Wednesday, May 04, 2011 7:50 AM
    Thursday, April 28, 2011 6:33 AM
    Moderator
  • Or enable Change Data Capture

    Thursday, April 28, 2011 6:34 AM
  • Using the below tsq, you can find out when was a table last updated -

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID('Replace your DB Name here')
    AND OBJECT_ID=OBJECT_ID('Replace your Table Name here')
    


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Thursday, April 28, 2011 8:07 AM
  • Hi Vinay,

    Thanks for your script. It works fine. But also, I would like to know which login/user modified that data.

    Thanks,

    Mahesh

    Thursday, April 28, 2011 8:18 AM
  • if you havent set up your own auditing then there is no way to get the information you want. If you have the default trace running you can use the following to get some information:

    DECLARE @tracefile NVARCHAR(256)
    SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('\',REVERSE([path])))+ '\log.trc' FROM sys.traces WHERE [is_default] = 1)
    
    SELECT
     gt.[ServerName]
    ,gt.[DatabaseName]
    ,gt.[SPID]
    ,gt.[StartTime]
    ,gt.[ObjectName]
    ,gt.[objecttype] [ObjectTypeID]--http://msdn.microsoft.com/en-us/library/ms180953.aspx
    ,sv.[subclass_name] [ObjectType]
    ,e.[category_id] [CategoryID]
    ,c.[Name] [Category]
    ,gt.[EventClass] [EventID]
    ,e.[Name] [EventName]
    ,gt.[LoginName]
    ,gt.[ApplicationName]
    ,gt.[TextData]
    FROM fn_trace_gettable(@tracefile, DEFAULT) gt
    LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.subclass_value = gt.[objecttype]
    INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id]
    INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id]
    WHERE gt.[spid] > 50
    AND gt.[objecttype] <> 21587 --Statistics
    AND gt.[databasename] <> 'tempdb' 
    
    

     


    Jon
    Thursday, April 28, 2011 8:25 AM