[dba-SQLServer] Audit table - How to capture user who deletedrecord

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
>
>