[dba-SQLServer] Audit table - How to capture user whodeletedrecord

We don't mark anything. We want to use a trigger to populate the history table using the virtual INSERTED and DELETED tables. Works great for inserts and updates, but modified_by doesn't get tracked anywhere on a delete. On an insert or update it gets tracked in the INSERTED table.
I don't have any issue with records being deleted in that table, I just need to keep track of who does what.
We're going to try a workaround using the SPID that's in our LoginTrack table. Hoping that will give us what we need.
Thanks for all your ideas! 
Betsy
 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, October 28, 2009 1:50 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Audit table - How to capture user whodeletedrecord

How do you mark edits or insertions?

One thing to do is never to allow deletions. Instead mark a record as "deleted" and filter it from views.

On Wed, Oct 28, 2009 at 10:36 AM, Powlen, Betsy <Betsy.Powlen at stanleyassociates.com> wrote:
> Thanks, Dan. The problem is, people aren't directly users on the 
> database. The actual user data is on another database and they go 
> through a bunch of security hoops to get to the main application. From 
> there, there is a link to our application.   Normally, whenever we 
> need a user name, we use a parameter, but apparently that doesn't work 
> with triggers. The only way I can think off hand is to update the 
> table with the session id first, but was wondering if that is too big 
> of a strain on the system as we will be implementing this on all our 
> editable tables.
> Thoughts?
>
>
> Betsy Powlen
> Stanley Associates
> 3800 Fettler Park Drive
> Dumfries, VA  22025
> Office: (703) 441-4965
> Cell:    (540) 498-0836
> betsy.powlen.ctr at usmc.mil
> betsy.powlen at stanleyassociates.com
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan 
> Waters
> Sent: Wednesday, October 28, 2009 1:09 PM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] Audit table - How to capture user who 
> deletedrecord
>
> Hi Betsey,
>
> If people are logging in with a user name and password, then you can 
> use 'CurrentUser'.  This is an access function.
>
> Otherwise, you can get the PC Name that someone is using with this
> function:
> 'Environ("ComputerName")'.
>
> Good Luck!
> Dan`
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of 
> Powlen, Betsy
> Sent: Wednesday, October 28, 2009 11:54 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Audit table - How to capture user who deleted 
> record
>
> Hello everyone,
> I'm creating an audit table for our application. I had no problem with 
> the trigger for an insert or update, but when a user deletes a record 
> I can't figure out how to capture that user.
> I can't use system_user, that only shows the user name we created to 
> access the application.
> Any ideas?
>
> Betsy Powlen
> Stanley Associates
> 3800 Fettler Park Drive
> Dumfries, VA  22025
> Office: (703) 441-4965
> Cell:    (540) 498-0836
> betsy.powlen.ctr at usmc.mil <blocked::mailto:betsy.powlen.ctr at usmc.mil>
> betsy.powlen at stanleyassociates.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com