Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit Trail Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 11:38 AM
Points: 2, Visits: 20
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



Post #1438079
Posted Tuesday, April 2, 2013 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:47 PM
Points: 12,910, Visits: 32,020
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1438089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse