Audit Trail
-
Hi,
I have a question about how to approach a audit trail requirement for my application. I need to perform audit trails on certain situations so I would like to automatically log with the data records. The data I will like to log are the program name, user, host of the session performing the inserts, updates, deletions of records.
I am taking this approach because based on the system requirements I know for example that some inserts should only be done via programX so if I see an insert from a different program I should flag that record on my audit trail report. I see were I can use the system DM to get the information so could modified my store procedures to get the information and update the necessary fields in the records. Or should I create triggers to automatically handle this.
thanks
-
from the way it sounds, a trigger sounds like the solution, especially if you need to know WHAT was changed by the stray process/program.
Since the Application Name and the Whostname can be modified, faked or blanked out if entered explicitly in the connection string, you might want to alos catch the IP address; 2008+ has the new ConnectionProperty Functions to help with that:
--for 2008 and above:
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
CURRENT_USER AS CurrentUser,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
ConnectionProperty('net_transport') AS 'net_transport',
ConnectionProperty('protocol_type') AS 'protocol_type',
ConnectionProperty('auth_scheme') AS 'auth_scheme',
ConnectionProperty('local_net_address') AS 'local_net_address',
ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
ConnectionProperty('client_net_address') AS 'client_net_address',
ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply