Capturing the user and time of a delete with a DELETE trigger

Question

Capturing the user and time of a delete with a DELETE trigger

Robby Dillard

SSC Veteran

Points: 261
More actions
December 27, 2005 at 10:14 am

#89091

How can I capture the user and the time of a delete via a DELETE trigger when using a single logon account to access the database?
For example, our online system requires logon credentials. These credentials are not used to establish a connection to the database, but are used for auditing purposes. We use a single account signon to get access to the database.
So if I wanted to record the fact that "dillardrobby" signed on and deleted a record, how can I record that it was "dillardrobby" who performed the delete instead of the account that was used to log on to the database?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

I think your application may check the statements that are sent to the database. If the Delete keyword is detected then an insert statement is going towards you custom Audit table with the record with the application user name.

As an alternative you may want to use Windows authentication and check what is a Windows user who is logged on the computer. Or your application code may use tips from the article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;111544

"How to retrieve current user and domain names on Windows NT, Windows 2000, or Windows XP"

Article ID

:

111544

Regards,Yelena Varsha

Robby Dillard SSC Veteran Points: 261 More actions · Answer 2

Thanks for your reply, but I *must* record the username of the user provided credentials in the audit record.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

You won't be able to do it at the database level. As far as the database is concerned there is only one account accessing the database.

You'll have to figure a way for your application to determine if a DELETE is being passed and if so, grab the user information.

-SQLBill

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 4

Add the "user provided credentials" access to the trigger by adding this column to each table to be audited, populate this column from your app that should already have access to this value, then use this "audit_user" value from the inserted table for your audit table insert.

Andy

Robby Dillard SSC Veteran Points: 261 More actions · Answer 5

So, I need to perform and update to the table to be audited prior to performing the delete so that the "audit_user" will be available in the INSERTED table? Do I understand you correctly?

noeld SSC Guru Points: 96590 More actions · Answer 6

There is another way, which is a bit simpler and is to use sp to perform DML and on the "delete" stored procs your app can pass the "audit credentials" in as a parameter and no trigger will be needed

Cheers,

* Noel