Audit Data

Jan 18, 2005

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 6 Replies


ADVERTISEMENT

Database Audit Specification To Audit Select On Certain User And Table

Nov 1, 2014

I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query

"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"

It return a value at which time the query has done

after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on

View 1 Replies View Related

Audit Logon / Audit Logoff Problem With SQL 2K

Jan 18, 2006

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 6 Replies View Related

Data Base Audit Options

Mar 12, 2008

Hi All,

Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).

I dont want to place any trace or triggers, is there any in-built option.

View 4 Replies View Related

Moving Data For Audit - Best Practice

Oct 17, 2005

Here is the situation:Tables:AccountAccountDocumentAccountTestThen we have the following "Activity" table refencing "Account" tableabove:Table: ActivityColumns: ActivityID, AccountNo,...when "Activity" with accountno is created, we'd like to able to takesnapshop or copy all Account related data records from tables: account,accountdocument, accounttestand store it somewhere.This way even if account, accountdocument and accounttest tables changelater, then "Activity" accountno integrity in maintained.Do we need to create or replicate similar tables to store data foraudit?or Which other ways can this be achieved?

View 3 Replies View Related

Data Type In Audit Record

Mar 30, 2007

Hi,I want my application to audit any data changes (update, insert,delete) made by the users. Rather than have an audit table mirroringeach user table, I'd prefer to have a generic structure which can loganything. This is what I've come up with:TABLE: audit_record*audit_record_id (uniqueidentifier, auto-assign, PK) - uniqueidenfiier of the audit recordtable_name (varchar) - name of the table where the action (insert/update/delete) was madepk_value (varchar) - primary key of the changed record. If the PKitself has changed, this will store the old value.user_id (varchar) - user who changed the recorddate (datetime) - date/time at which the change was madeaction (int) - 0, 1 or 2 (insert, update, delete)TABLE: audit_column*audit_record_id (uniqueidentifier, composite PK) - FK tocdb_audit_record table*column_name (varchar, composite PK) - name of the column with changeddatanew_value (text?) - value after the changeSo every column which changes has its new value logged individually inthe audit_column table. However, I'm not sure what data type thenew_value column should have. The obvious answer (to me) is text, asthat can handle any necessary data type with the appropriateconversion (we don't store any binary data). However, this table isgoing to grow to millions of records and I'm not sure what theperformance implications of a text column will be, particularly giventhat the actual data stored in it will almost always be tiny.Any thoughts/recommendations/criticism would be greatly appreciated.ThanksAlex

View 5 Replies View Related

Converting Audit Data To Create History

Apr 9, 2008

Hi

I have a table called my_history that has columns like this


column_name , old_value, new_value, key, date
bankaccount 30 50 1 01-Apr-2008
bankbalance 10 14 2 04-Apr-2008


and so on............

The history table is populated using triggers

The main table called my has a structure like


bankaccount bankbalance bankname name address key
50 50 xyz abc ford 1
30 14 abc xyz east 2


Now using this information can i reconstruct the records in my table before the update happened ?

I am finding it very difficult to do this is there a way to do this in t-sql ?

The problem is my_history table where the column_name keeps on varrying

regards
Hrishy

View 7 Replies View Related

Transact SQL :: DB Audit File That Gets Populated With Data As Activity On DB Goes On

Aug 25, 2015

writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file

SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default)
order by event_time desc
GO
--DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]

[code]...

View 2 Replies View Related

Reporting Services :: SSRS Report Data Logging Process / Audit Trail

Jun 30, 2015

1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process. 

2. Information like WHO and WHEN printed the report and WHAT data was viewed?

View 5 Replies View Related

Piping Data From A Table To An Audit Table

Nov 17, 2005

Lets say I have a table in which I want to audit, so I create a trigger for this table. Like

CREATE TRIGGER
on table
AFTER UPDATE
AS
INSERT INTO audittable (Changes Made to other table)
GO

How would I get the changes is all I'm asking.

View 1 Replies View Related

Audit

Nov 9, 2005

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 1 Replies View Related

C2 Audit

Jan 30, 2008

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 1 Replies View Related

Audit Query

Feb 29, 2008

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 1 Replies View Related

Audit Trails

Apr 19, 2002

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 1 Replies View Related

Audit Trail For BCP

Mar 19, 2001

Hi,
Is there anyway I can audit the data imported by BCP or DTS into the table ?

Thanks,
Mano.

View 1 Replies View Related

Audit Log Ideas

Nov 28, 2000

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 4 Replies View Related

Audit Logins

Jan 18, 2001

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 1 Replies View Related

Audit Question

Jan 23, 2001

Is there a way to audit a change on a column besides using trigger?

View 3 Replies View Related

USER Audit

Dec 1, 1999

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 1 Replies View Related

Audit Trails

May 27, 1999

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 1 Replies View Related

Audit Delets

Aug 26, 1999

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 1 Replies View Related

Audit Use Of Databases

Jul 10, 2002

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 1 Replies View Related

Audit Trail For MS SQL

Nov 9, 2006

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 2 Replies View Related

SQL Secuirty Audit

Oct 27, 2004

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 2 Replies View Related

Database Audit

May 2, 2006

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 2 Replies View Related

SQLServer2000- Audit - ALL

Jun 23, 2006

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 1 Replies View Related

Query For SQL Audit

Nov 30, 2007

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 3 Replies View Related

Which Is Better For An Audit Trail

Apr 27, 2004

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 4 Replies View Related

SQL Audit Tool

May 10, 2007

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 5 Replies View Related

Audit Trigger

Jun 5, 2007

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 2 Replies View Related

SQL Login Audit Log

Jul 23, 2005

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 6 Replies View Related

Transaction Log - Audit

Jul 20, 2005

Is it possible to find out from the transaction log, who deleted a recordand when?

View 1 Replies View Related

Triggers Audit

Jul 20, 2005

I need audit triggers that change columns value in the same recordthat fire trigger.I need how to do..Thanks..

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved