Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Audit Trail For MS SQL


Hi folks. Any ideas on the best way to creat an audit trail for ms sql 2000?

I want to capture all tables affected by UPDATE, INSERT and DELETE queries.

Any help would be appreciated!

Many thanks!
Kunal


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Audit Trail...
Hi...A much lamented question, I guess..I'm trying to create a simple audit trail.log the changes to an SQL 2000 table, so that they are written into amirror table. The entire record, only the updated one, i.e. if sayonly one field changes, the audit table will be inserted with onerecord that has one field changed. if the record has been deleted, itstill will be written.I'm not worrying about additional fields to the audit table containingdescriptive flags of what action took place yet. I just want themirror image for starters.I got the script of the 'create table' off Query analyzer. created theaudit table.the trigger looks like this:CREATE TRIGGER dt_tbl1_auditon tbl1for insert, update, deleteASinsert into tbl1_auditselect * from insertedthe table has about 50 fields or so, so I tried to make do with *'s.didn't work, so I tried copying and pasting the explicit list of fieldnamesinstead (though I'm not sure why it needs that if the two tables areidentically structured).in either case, if I update any field on the audited table, I get thiserror:(after getting the warning that the results may take a long time toprocess etc, the original table has over 100,000 rows)"another user has modified the contents of this table or view,the database row you are modifying no longer exists in the databasedatabase error: insert error:column name or number of supplied values does not match tabledefinition"I'm not sure what's wrong, the two tables are identical (I copy pastedthe create table script with no changes). no other users except me onthis database.i've removed all constraints and indexes from the audit table.thanks

View Replies !   View Related
Audit Trail..
Hi...A much lamented question, I guess..I'm trying to create a simple audit trail.log the changes to an SQL 2000 table, so that they are written into amirror table. The entire record, only the updated one, i.e. if sayonly one field changes, the audit table will be inserted with onerecord that has one field changed. if the record has been deleted, itstill will be written.I'm not worrying about additional fields to the audit table containingdescriptive flags of what action took place yet. I just want themirror image for starters.I got the script of the 'create table' off Query analyzer. created theaudit table.the trigger looks like this:CREATE TRIGGER dt_tbl1_auditon tbl1for insert, update, deleteASinsert into tbl1_auditselect * from insertedthe table has about 50 fields or so, so I tried to make do with *'s.didn't work, so I tried copying and pasting the explicit list of fieldnamesinstead (though I'm not sure why it needs that if the two tables areidentically structured).in either case, if I update any field on the audited table, I get thiserror:(after getting the warning that the results may take a long time toprocess etc, the original table has over 100,000 rows)"another user has modified the contents of this table or view,the database row you are modifying no longer exists in the databasedatabase error: insert error:column name or number of supplied values does not match tabledefinition"I'm not sure what's wrong, the two tables are identical (I copy pastedthe create table script with no changes). no other users except me onthis database.i've removed all constraints and indexes from the audit table.thanks

View Replies !   View Related
Which Is Better For An Audit Trail
A table that stores all [updated | deleted] transactions in a database
i.e.
TableName, TableId, ColumnName, ValueType, NewValue, OldValue, DateChanged

or

A copy of each individual table, which could add up to a lot of tables

View Replies !   View Related
Audit Trail For BCP
Hi,
Is there anyway I can audit the data imported by BCP or DTS into the table ?

Thanks,
Mano.

View Replies !   View Related
Typical Audit Trail ?
I tried to implement triggers for filling audit-trail table on this way.Everything works fine as long as I don't update the primary key field value.When I try to update PK value, an error occures.The code is the following:CREATE TRIGGER NameOfTheTriggerON dbo.TableName FOR DELETE, INSERT, UPDATEAS BEGINdeclare@type varchar(10) ,@UpdateDate datetime ,@UserName varchar(128)if exists (select * from inserted) and exists (select * from deleted)select @type = 'UPDATE'else if exists (select * from inserted)select @type = 'INSERT'elseselect @type = 'DELETE'select @UpdateDate = getdate() ,@UserName = system_user/* this code is repeting for every field in the table*/if update (TableName) or @type = 'DELETE'insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,UpdateDate, UserName, type)select 'TableName', convert(varchar(20), 'FieldName'),convert(varchar(1000),d.FieldName), convert(varchar(1000),i.FieldName),@UpdateDate, @UserName, @typefrom inserted ifull outer join deleted don i.PrimaryKeyFieldName = d.PrimaryKeyFieldNamewhere (i.FieldName<> d.FieldName or (i.FieldName is null and d.FieldName isnot null) or (i.FieldName is not null and d.FieldName is null))ENDHow to slve the problem with updated (changed) primary key values?What is the typical code for audit-trail triggers?Thanks.

View Replies !   View Related
Audit Trail Triggers
Hello.I tried to implement audit trail, by making an audit trail table with thefollowing fileds:TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.Triggers on each table were set to do the job and everything was fine exceptthat in the audit trail you couldn't know which row exacltly wasupdated/inserted/deleted...Therefore I introduced 3 additional columnes(RowMark1, RowMark2, RowMark3) which should identify theinserted/updated/deleted row.For example, RowMark1 could be foreign key, RowMark2 could be primary key,and RowMark3 could be autonumber ID.But, when I have several rows updated, RowMark columnes values are identicalin all rows in the audit trail table! What is wrong with my code, and how tosolve it ?Thank you in advance!CREATE TRIGGER Trigger_audit_TableNameON dbo.TableNameFOR DELETE, INSERT, UPDATEAS BEGINdeclare @type nvarchar(20) ,@UpdateDate datetime ,@UserName nvarchar(100),@RowMark1 nvarchar (100),@RowMark2 nvarchar (100),@RowMark3 nvarchar (100)if exists (select * from inserted) and exists (select * fromdeleted)select @type = 'UPDATE',@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.IDfrom deleted delse if exists (select * from inserted)select @type = 'INSERT',@RowMark1=i.ForeignKeyField,@RowMark2=i.PrimaryKey Field,@RowMark3=i.IDfrom inserted ielseselect @type = 'DELETE',@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.IDfrom deleted dselect @UpdateDate = getdate() ,@UserName = USER/*The following code is repeated for every field in a table*/if update (FieldName) or @type = 'DELETE'insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)select 'Descriptive Table Name', convert(nvarchar(100), 'DescriptiveField Name'),convert(nvarchar(1000),d.FieldName),convert(nvarchar(1000),i.FieldName),@UpdateDate, @UserName, @type, @RowMark1, @RowMark2,@RowMark3from inserted ifull outer join deleted don i.ID = d.IDwhere (i.FieldName <> d.FieldNameor (i.FieldName is null and d.FieldName is not null)or (i.FieldName is not null and d.FieldName is null))END

View Replies !   View Related
Simple Audit Trail
Hi, anyone have links i can go to about audit trail?i have been on the internet an the example are confusing.

View Replies !   View Related
Audit Trail Using Asynchronous I/O
We're looking for a solution to an audit trail issue. Our business people are looking to track each column value that changes(before and after images) for every table on our database as well as the userid that changed the data and when it was changed. Are there any methods that have been employed by other sites to track this level of detailed changes without resorting to triggers for each table and has anyone worked out a way for this audit trail writing to be handled asynchronously within SQL Server?

View Replies !   View Related
SQL 7 Audit Trail For Logfiles
Does anyone know of any software that provides the capability of auditing
SQL transactions from the transaction log files? We recently had some records
deleted from a table and would like to know when, how, and who. I thought that this info might be available in the transaction log files.

View Replies !   View Related
Audit Trail For Database Changes
Hello!

Can anyone help? I require a method to retrieve the history behind the creation and changes made to database objects.

I don't mean when a user updates a table, but getting version numbers and dates changed of tables and stored procs etc.

Is there a way to do this?

Please reply

View Replies !   View Related
Best Way Of Creating Audit Trail
Hi!

I have a Mailout table with a MID, ADDRESS, CITY, POSTCODE linked to another table called History which contains events that are associated with each Mailout record. I need to create an audit trail based on the MID of the mailout table that would tell me how many records are currently in event 1,in event 2 etc.

I'm thinking of creating an audit trail table that would store the data as well as create triggers to check on any change(add/deletions/updates) of the status of each record.

Is this the best way of doing it? Or are there any other ways of achieving an audit trail?







$3.99/yr .COM!
http://www.greatdomains4less.com

View Replies !   View Related
Audit Trail Trigger
Hi all,

I'm trying to create a audit trail trigger. I'm new to SQL Server. For simplicity sake, let's just say I have a table like the following:

Table1

FieldA
FieldB

And the audit table is:

Table1_Audit
FieldA
FieldB
Operation (Insert/Update/Delete)
Operator (Username)
Op_Date (GetDate())

Ok, simple enough. Now, I'm using Studio Express andthere are several templates available. By default this is the one I get:


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>

ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>

AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for trigger here

END

GO


Not sure if I need all of this information? Examples I've found on the web look very confusing...ugh. I looking for simple (if possible).

Many thanks in advance,

Mark

View Replies !   View Related
Any Good Audit Trail Examples?
Hi Guys!

What's the best way to keep an audit trail of every insert, update, and delete of a certain table? Any example code out there? I'm thinking in terms of a trigger for each event, for instance, the update trigger would insert a new record into the audit table with a field for each column in the deleted table and a field for each column in the inserted table.

Thanx

Dave

View Replies !   View Related
Database Audit Trail Question....
Hi All....

First a bit about me. I'm a developer in Atlanta. My background is mostly in Unix, but am working in NT at this job. I am gearing my skills towards enabling products on the web for people. I have experience in PHP, HTML, Javscript, C/C++, MySQL, a little Oracle and now getting fimilar with SQL Server.

What i'm trying to do....
Track all changes to data in the DB.

Why i'm doing it....
Banking application, any account changes need to be logged with which employee made the change.

How I want to do it....
Currently the app is a web app that accesses VB for the backend. There is a single SQL Server user accessing the DB. I want to use triggers on INSERT, UPDATE, and DELETE to copy the new row to any audit table. This will be identical but with user, action and time.

Problem....
Single user connection will hide acutal app userid. I want to have the VB app get the "SQL Server Session ID" (if one exists) and store the app_userid in a table with the session ID for cross reference by the trigger.

Question:
Does SQL Server have a "session id" for multiple connections for a single user? Where is it located? Can VB access this information?


Thanks,

Brian

View Replies !   View Related
Apology In Advance For Multiple Post Re AUDIT TRAIL...
some sort of server problem, I was sure it didn't post because itwouldn't confirm it, only crash. I guess it did...sorry

View Replies !   View Related
Maintain Audit Trail Of Access To SQL Server 2000 Database
Is there any way to maintain audit trail of access to my SQL server 2000 database by any user ?? I need to log the timestamp of anyinsert/update/delete to any record in a table within the database by the user.

Alex

View Replies !   View Related
Audit Logon / Audit Logoff Problem With SQL 2K
I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???

View Replies !   View Related
Audit Category Numbers Failure Audit 4
I am getting a new event every few seconds for a Failure Audit on a couple of user accounts for Event ID:18456.
Category 4.

I did find a list of numbers
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
but it didn't list what number 4 is.

Does anybody know what number 4 is.

Cheers

View Replies !   View Related
Can't Download Ms Sql 2005 Free Trail Version
hi was looking forward to downloading the 180 day trial version of ms sql server 2005. Signed up and everything.when it loaded the download page it only has the 2help files listed. Has the free trial ended? if i download the full express version does that have a free trial? info@uktattoostudios.co.uk

View Replies !   View Related
Can't Download Ms Sql 2005 Free Trail Version
hi was looking forward to downloading the 180 day trial version of ms sql server 2005. Signed up and everything.when it loaded the download page it only has the 2help files listed. Has the free trial ended? if i download the full express version does that have a free trial?

View Replies !   View Related
Upgrade Trail Version With A Full Version Of Ms Sql-server
We want to licence a trail version 2005ENwith an fullversion of MS Sql server 2005EN. Is that possible?

Conworx

View Replies !   View Related
Audit
I want to register into a table each time a user creates, modifies or deletes any object in a database. It's not possible to add a trigger to the sysobjects table. What can I do?

View Replies !   View Related
C2 Audit
Is it possible to enable c2audit on MSDE 2000 ?

Also can someone give me idea about ideal implementation of Audit enable for Production Servers ?

View Replies !   View Related
AUDIT ALL - - But Who Are YOU?
Basically, I have two servers. One is the publisher/distributer and the other is the subscriber. On the subscribing server, I have written some triggers that monitor when users perform 'update', 'insert', and 'delete' statements.

The problem is: I am in search of a way to distinguish between users and replication. I am looking for a way to write replication
statements to a different log than the user-generated statements.

Obviously at this point, current_user, system_user, user, and session_user cannot help me out.

Any advice is appreciated. Thanks!

View Replies !   View Related
C2 Audit
Hello,

I enabled the C2 Audit option for my SQL server and it is working allright. i am trying to figure out how can i configure the audit to run for specific databases/tables only. I have several databases on the server but I just want to audit one table in one database for PCI compliance rules.
Any recommendations?

Thanks,
Tony

View Replies !   View Related
Audit Query
Good morning,
 Im having a little problem with this report I need to generate, so I thought I would ask for some advice.
I have 2 tables. The 1st is the actual table and the 2nd is the log table (Employee; Employee_log). the '_log' table is an image of the Employee table except it has 4 extra fields (recID,  last_updated_employee_id, operation and operation_date) recid being the PK of the log table.
I need to generate a report that contains some thing like the following:



Table_Name

Column_Name

Old_Value

New_Value

Employee_Modifier

Operation

Operation_Date



Employee

LastName

Reid

Blevins

Jessica Bluff


UPDATE

2/2/2008



Employee

FirstName

Bison

Blison

Jessica Bluff

UPDATE


2/1/2008



Employee

EmployeeID



1234

Jessica Bluff

INSERT

2/1/2008



Employee

EmployeeID



75

Bill Thompson

DELETE


1/28/2008


 To do this, I compare the Employee table to the Employee_log table. If I find changes (the two columns do not equal), I add that columnName and the column value for the regular table(new_value) and the column value for the log table(old value).  If anyone has a solution or some sql to help me out, it would be greatly appreciated.  (A query that will give me each columnName with the value per row would help; Somehow possibly joining my Employee table with 'INFORMATION_SCHEMA.COLUMNS' ??)  Thanks! 

View Replies !   View Related
SQL Login Audit Log
Hi, AllI use SQL Server 2000 and Win 2003 Server. I try to create a SQL ServerLogin audit log using Profiler, but cannot find the tools. I looked at theWindows Event Viewer, it only logs Windows authentications users for SQLServer not SQL Server users (Mixed mode users).Please help on creating SQL Server Login log (username,password and time).ThanksKai

View Replies !   View Related
Transaction Log - Audit
Is it possible to find out from the transaction log, who deleted a recordand when?

View Replies !   View Related
Triggers Audit
I need audit triggers that change columns value in the same recordthat fire trigger.I need how to do..Thanks..

View Replies !   View Related
SQL Secuirty Audit
Hi Folks,

Have a scenario where we have to audit all our databases and servers for changes in security accross the servers.

We have a central montioring server where we pull SQL metadata at regular intervals. In this instance we are looking to have before and after snapshots of the SQL system tables.

For the logins this is fine as there is a last updated field in the syslogins table. We can tell when a new user has been added , remmoved or the login has changed ie the login as been added to sysadmin fixed server role... etc Perfect !!!

What Im trying to work out now is how I can do this for object level permsisions. Have looked at sysprotects but no joy. If a user or a role has been granted select or update on a table... How can I tell based on before and after snap shots of the system tables what permissons have changed and whom have they changed for...

Help ......

View Replies !   View Related
Audit Data
Hi all,
I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
Thanks,

View Replies !   View Related
Database Audit
Does MSSQL 2000 and above have an auditing feature? We have a requirement for tracking activity and history on a particular table. I realize this can be accomplished by a simple trigger but I was wondering if MSSQL had a feature that would accomplish this.

joe

View Replies !   View Related
SQLServer2000- Audit - ALL
I turned the Audit ALL option on SQLServer instance "security" tab and restarted the SQLServer but do not see any information logged in SQLServer Logs though I tried to access databases and logged in a couple of times through Query Analyzer. Why is that no logging happened and how can I get this fixed?

Any help is appreciated.

Vinnie

View Replies !   View Related
Query For SQL Audit
Hey everyone,

This is my first question here for a long while, so be extra nice ;)

I am doing an audit on some of my sql server 2000s and I would to know if its possible to automate the collection of some of the data.

Firstly I would like to be able to query the domain account that SQLSERVER and SQLAGENT are running on, in my case the agent and service account will alaways be the same, but the account name maybe different depending on what server it is.

Secondly I would like to be able to query whether the account SQL server is running on is local admin of the server...I know some of you will say the SQL account has to be local admin but with the right registry and folder level permissions it is not necessary for the account to be local admin. This was a requirement from a very strict security audit.

View Replies !   View Related
Audit Software
Is there any third party software available that can automatically track changes to tables?

View Replies !   View Related
Audit Delets
Is there away to track which user had deleted and object(table),
the transaction log has that information but you can't read it and
the error log doesn't log such info. Any advice would be appricated, thanks.

View Replies !   View Related
Audit Table
I would like to know if there is a way to check to see when was last time Table or store procedure was modified. I check in sysobjects, only find when the objects were created.
could some help. Thank you.

View Replies !   View Related
Counter & Audit
Need advice on how to best tackle this task. l've created a table called Run_Audit with the following fields
Run_Counter,Run_Date,No_Of_Records,Run,Status.

Run_Counter will be a number that increaments by 1 each time the procedure runs
Run_Date this will default to the system date l can uset the getdate function l guess
No_Of_Records will be the number of records grouped by pay_frequency
Run_Status was it successful ? Should insert a message eg

declare @Comp_msg varchar(255)
declare @fail_msg varchar(255)

set @Comp_msg ='The run completed successfully'
set @fail_msg ='The run did not complete successfully'


my problem is l dont know where to start with this procedure which is a modification of the Procedure below.Please
advice.


CREATE Procedure Collections_Cats_Modified
AS
BEGIN

Declare @Today int
Set @Today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT CASE
WHEN Pay_Frequency = 4 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 3 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 2 And Payday =@Today THEN @Today
WHEN Pay_Frequency = 1 And Payday =@Today THEN @Today
ELSE 0
END AS "Pay_day",
n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment
FROM Transaction_Record tr
INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_Sheet.Payday = @Today
And Pay_sheet.Pay_Frequency Between 1 And 4
And Pay_Sheet.Payday Between 1 And 31

OPEN Collections_Cats_Cursor
-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END
CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View Replies !   View Related
Audit Use Of Databases
Can anyone help me audit connections to databases?

My objective is to tidy a poorly maintained set of servers - especially permissions. (Any suggestion what-so-ever, would be welcome)

Specifically I'm now looking to audit who accesses which databases. As a first step I just want to be able to record database open actions.

I think profiler can help.
My aim is a list of NTuser, Server, Database, When

I've tried profiling Event Object:Opened but NOTHING happens.
Other profile events are OK.

So the simple questions are, what makes this event fire or what is the approriate event (or other method) to acheive this objective.

Note I've looking into auditing - but this doesn't provide me with which database is accessed.

I could, I suppose, use Locks acquired

View Replies !   View Related
Audit Trails In SQL 7?
I seem to recall reading that SQL 7 will have comprehensive
auditing features. Is that correct? I do have a SQL 7 beta 3 installed,
but I could not find anything that would allow me to generate auditing trails.

Please let me know if auditing is available, and if so, where it is.

Thanks.

View Replies !   View Related
Audit Trails
I am currently developing a system which uses SQL Server 7 as its repositry. One of the systems requirements is the ability to be able to record any changes made to the data, and by whom. In other words I need to store the before and after with a userid.

Has anyone any experience with the matter.

Many thanks

Martin Fisher

View Replies !   View Related
USER Audit
hi,

does anyone know how i can audit a servers login id's and tell the last time it was used. i have just gain about 8 sql servers with a bunch of users that i know are no longer around. so i am trying to trim out dead id's

thanks for any help !!


k ingram
cellstar corp.

View Replies !   View Related
Audit SA Access.
Does anyone know of a way to audit JUST success/fail logins for the 'sa' user? Does SQL server 7 have similar object level audit trails as Sybase adaptive server? Does anyone know of a solution to this either by built-in feature, T-SQL scripting or a third-party product.

Thank you

View Replies !   View Related
Audit Log Ideas
I have tried to make my basic audit log do more, but i haven't gotten very far;

In my basic audit log, i record this information:

table
type of change
field modified
old value
new value
db user
date/time

This audit records everything, which is great, but it cannot relate information when i go back to analyze the changes; for example, when a "directory" record is added, a user's information may be entered into several different tables, such as:

name (different table)
addresses (different table)
phone numbers (different table)

If one wanted to look up the changes to addresses of a person in the directory based on the person's name, i could not do it with my existing audit log because the addresses would be in a different table than the name table and there is no relating data in the audit log to relate the address changes to a persons name;

What might be a solution? I have tried a few approaches and am at a loss;

Thank you!

--llyal

View Replies !   View Related
Audit Logins
Does anyone have any ideas how I can track when someone logged in and out of SQL Server and compile that information over a 3 month period?

View Replies !   View Related
Audit Question
Is there a way to audit a change on a column besides using trigger?

View Replies !   View Related
Audit Trails
Hello,

I wish to track changes made to a table, including login who made the change, time of change, etc, without having to change the existing table structure, stored procedures, application.
If anyone has any strategies (with a brief explanation) or articles they could point me to, it would be very much appreciated.

My thinking is to set up a trigger to write both old and new data to a audit table but considering different strategies may be helpful.

Thanks for your time,
Esox

View Replies !   View Related
Audit Triggers
Table1 has two columns col1 & col2. I have added 4 columns to one of the tables .the new columns added are user_inserted, date_inserted, user_modified & date_modified.

this is specification for trigger :
I need to insert user_inserted & date_inserted column values with current date when there is an insert on table. And I need to insert user_modified & date_modified values when there is update.

How can I code this trigger/triggers in best way performance-wise.

Thanks In Advance.

View Replies !   View Related
Audit Trigger
hi, im kinda new to sql and was just wondering if someone could help.

i need to create a trigger that saves all changes from a table(table1) to an audit table.

-table1-
item_code
item_price
item_description

View Replies !   View Related
SQL Audit Tool
Hi,

We are looking to purchase SQL Audit tool. Which one do you recommend for SQL Server 2000 Standard Edition and SQL 2005 Enterprise Edition.

Thanks,
Debi.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved