Discussion Locked

Auditrail Feature in SQL server

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I wanna have the Transaction record of all my project Master Tables in some table whih will be created in database.
The Columns of table I need is :
1. User_id
2. User_Name
3. Table_Name ( on which opertaion is performed)
4. Operation Performed
5. Date and Time of Operation
6. whether operatin is successful or not.

In oracle 8i there is a feature which automatically does this (Set Audit trail On )...but in SQL server I am unaware of any such feature. Please Help me out.

Even a trigger on particular table can do this operation. If so, please tell me HOW CAN I GET USER_NAME AND USER_ID who is performing operation on table.

1 reply

by g.albera

As far as I know there is no feature such as Oracle's Audit trail.

However you can execute:

SELECT CURRENT_USER

to get the name of current user
and

exec sp_helpuser

to get additional information such as:
UserName, GroupName, LoginName, DefDBName, UserID, SUserID

inside a trigger you can thus now table name (by design the trigger fires under a table), user id, user name and so on, current date and time (with GETDATE()function). for operation performed you may create a trigger for insert/delete/update.

Hope to be useful.

Regards,
Ing. Gianluca Albera

This discussion is closed to new replies.