26. dubna 2012 12:43
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.
26. dubna 2012 14:23
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
26. dubna 2012 17:30
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.
William F. Kinsley
- Navržen jako odpověď Rama UdayaMicrosoft Community Contributor 1. května 2012 14:27
27. dubna 2012 3:14
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
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.
29. dubna 2012 7:51
Probably tracing file, you can configure or using defaults.See the below example (originally written by Aaron) to capture autogrow eventDECLARE @path VARCHAR(255),
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
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/