locked
RE: SQL Logs RRS feed

  • Question

  • Kindly advise,

    Does SQL 2005 has a way of showing who(which account) made changes on which field in the database tables or generally the logs that show any changes made on SQL.


    Meshax

    Thursday, April 26, 2012 12:43 PM

Answers

  • No It wont log in the sql logs, you need to set up your own custom trciks to do so.

    how ever you can refer the Deafult trace which usually contains some of the Objcts that was altered,created and deleted.

    To know more about the default trace you can refer the below link

    http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

    Adding one more information Incase if you want to see the schems change then you can perform below steps to view-

    To Obtain an report on the schema changes using SQL Server Management Studio

    1. Connect to the  SQL Server by giving your SQL server Name/instance name.

    2. After successful connection one should see a Summary Window on the right side of the Object Explorer.

    3. Click on Reports button and select 'Schema Changes History' from the dropdown there you go to view the Modifications.

    • Edited by Rama Udaya Friday, April 27, 2012 7:59 AM
    • Proposed as answer by Rama Udaya Tuesday, May 1, 2012 2:27 PM
    • Marked as answer by amber zhang Friday, May 4, 2012 4:19 AM
    Friday, April 27, 2012 3:14 AM
  • Probably tracing file, you can configure or using defaults.See the below example (originally written by Aaron) to capture autogrow event

    DECLARE @path VARCHAR(255),
    @cmd VARCHAR(300);
    SET @path = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\';
    SET @cmd = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log*.trc' 
    SET @cmd='dir /b ' + '"' + @cmd  + '"' 


    DECLARE @files TABLE(fn VARCHAR(64));

    INSERT @files EXEC master..xp_cmdshell @cmd;
    SELECT * FROM @files
    DELETE @files 
    WHERE fn IS NULL;SELECT e.DatabaseName, e.[FileName],
    e.SPID, e.Duration, e.StartTime, e.EndTime,
    FileType = CASE e.EventClass
    WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END,
    [TraceFile] = f.fn FROM @files f CROSS APPLY
    fn_trace_gettable(@path + f.fn, DEFAULT) e
    WHERE e.EventClass IN (92,93) ---autogrow


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Rama Udaya Tuesday, May 1, 2012 2:27 PM
    • Marked as answer by amber zhang Friday, May 4, 2012 4:19 AM
    Sunday, April 29, 2012 7:51 AM

All replies

  • As far as I know : No.

    You could generate that tracing information by writing your own triggers on the system tables (quite a scary job).


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, April 26, 2012 2:23 PM
  • Depending on the size, number transaction/sec, concurrent users, etc. you have some options. In a smaller and low transaction rate DB, Triggers could work well and are easily to work with, but you need to careful about triggers getting dropped or disabled.

    Generally I would recommend use SQL Events (SQL DDL Triggers)  or SQL Profile traces, in either case you can create detailed filters and it will have the lowest impact; However, regardless of which approach you chose, there will be some impact on performance. I have found SQL Events/SQL Profiler to have the least impact, but more difficult to report if you need to parse the text(SQL) field.

    In either case, be prepared the the large amounts of data your audit log/tables will contain and how fast they will grow.

    Bill 

     


    William F. Kinsley

    • Proposed as answer by Rama Udaya Tuesday, May 1, 2012 2:27 PM
    Thursday, April 26, 2012 5:30 PM
  • No It wont log in the sql logs, you need to set up your own custom trciks to do so.

    how ever you can refer the Deafult trace which usually contains some of the Objcts that was altered,created and deleted.

    To know more about the default trace you can refer the below link

    http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

    Adding one more information Incase if you want to see the schems change then you can perform below steps to view-

    To Obtain an report on the schema changes using SQL Server Management Studio

    1. Connect to the  SQL Server by giving your SQL server Name/instance name.

    2. After successful connection one should see a Summary Window on the right side of the Object Explorer.

    3. Click on Reports button and select 'Schema Changes History' from the dropdown there you go to view the Modifications.

    • Edited by Rama Udaya Friday, April 27, 2012 7:59 AM
    • Proposed as answer by Rama Udaya Tuesday, May 1, 2012 2:27 PM
    • Marked as answer by amber zhang Friday, May 4, 2012 4:19 AM
    Friday, April 27, 2012 3:14 AM
  • Probably tracing file, you can configure or using defaults.See the below example (originally written by Aaron) to capture autogrow event

    DECLARE @path VARCHAR(255),
    @cmd VARCHAR(300);
    SET @path = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\';
    SET @cmd = 'O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log*.trc' 
    SET @cmd='dir /b ' + '"' + @cmd  + '"' 


    DECLARE @files TABLE(fn VARCHAR(64));

    INSERT @files EXEC master..xp_cmdshell @cmd;
    SELECT * FROM @files
    DELETE @files 
    WHERE fn IS NULL;SELECT e.DatabaseName, e.[FileName],
    e.SPID, e.Duration, e.StartTime, e.EndTime,
    FileType = CASE e.EventClass
    WHEN 92 THEN 'Data' WHEN 93 THEN 'Log' END,
    [TraceFile] = f.fn FROM @files f CROSS APPLY
    fn_trace_gettable(@path + f.fn, DEFAULT) e
    WHERE e.EventClass IN (92,93) ---autogrow


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Rama Udaya Tuesday, May 1, 2012 2:27 PM
    • Marked as answer by amber zhang Friday, May 4, 2012 4:19 AM
    Sunday, April 29, 2012 7:51 AM