Delete Trigger

  • Hello everyone,

    Is there a way to capture the username to who is currently log in the application when a delete trigger has been fired? I want to know because I want to capture the username who deleted the data. Thanks!

  • SELECT SYSTEM_USER

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SELECT Original_Login()

    Just in case there's any impersonation going on.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Is CDC a new feature of SQL server 2008 be useful for such a capture ? Or change tracking ?

  • I think I did not stated clearly my post and I'm sorry for that.

    The issue is... A user logon into a machine using domain account ("domain_account"). Then the user logon into the application search and delete the data. The application account is "application_account". I want to capture the domain account which is domain_account NOT the application account so that I would know who deleted the data.

    Thanks in advance for your response.

  • Does the application pass anything to SQL Server to identify the original user? (login name, host name, etc)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nothing except the ID(primary key) of the data.

    I think I would need to pass the username and use the output clause. Would you think that make sense?. I'll try it now 🙂

  • eklavu (6/18/2012)


    I think I would need to pass the username and use the output clause. Would you think that make sense?. I'll try it now 🙂

    Doesn't work that way. Only columns in the table can be outputted

    I meant as part of the connection string (host name or something). If the app doesn't tell SQL who is connecting to it, SQL cannot know.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Unfortunately I dont think this would be possible - due to "Applications" using a generic "SQL Login" for transactions.

    The user level security is handled by the application interface and therefore only allows the authorised transactions through to SQL - which would then use the SQL details to process the transaction.

    You may need to look back a step and have some audit checking within the application itself ?

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • If you run

    select * from sys.sysprocesses WHERE spid = @@SPID

    does it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.

    Regards,

    Jan

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken (6/18/2012)


    If you run

    select * from sys.sysprocesses WHERE spid = @@SPID

    does it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.

    Regards,

    Jan

    Jan, the problem is it's an impersonation login from the App. Windows or SQL Login, it all comes in as the app's login. Really the only way I know to do this is to track the IP the connection's coming from, and that can get goofy if you're middle tiered via IIS or N-Tier objects.

    In general... no, not that I'm aware of, not for an impersonated app login. I'll be very happy to be proved wrong though, this has been a thorn in my side for years.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (6/18/2012)


    Jan Van der Eecken (6/18/2012)


    If you run

    select * from sys.sysprocesses WHERE spid = @@SPID

    does it give you what you need to know in the nt_domain and nt_username columns? I'm running this on SQL 2012 and have no SQL2K8 instance available at the moment, so I'm not sure whether this will work for you.

    Regards,

    Jan

    Jan, the problem is it's an impersonation login from the App. Windows or SQL Login, it all comes in as the app's login. Really the only way I know to do this is to track the IP the connection's coming from, and that can get goofy if you're middle tiered via IIS or N-Tier objects.

    In general... no, not that I'm aware of, not for an impersonated app login. I'll be very happy to be proved wrong though, this has been a thorn in my side for years.

    Evil :w00t: Craig, you are right. Didn't quite get that impersonation issue. Then again, I've never played with App roles before. Yeah, you are right, trying to figure out what the IP is where the request came from, and then possibly tying it back to AD might be a possible solution. Or maybe not?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Oops, I misspelled your name. Apologies, Kraig!

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Jan Van der Eecken (6/18/2012)


    Oops, I misspelled your name. Apologies, Kraig!

    LOL, at this point, either is acceptable. 😉 :w00t:

    It just depends on which of the twins you want to talk to... the evil one with his goatee or his occassionally shaved good brother. 😎


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (6/18/2012)


    Jan Van der Eecken (6/18/2012)


    Oops, I misspelled your name. Apologies, Kraig!

    LOL, at this point, either is acceptable. 😉 :w00t:

    It just depends on which of the twins you want to talk to... the evil one with his goatee or his occassionally shaved good brother. 😎

    Now which one of the two is you? I wouldn't mind shaving either.

    Got twins too, younger ones. One of them certainly doesn't need shaving. 😉

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 15 posts - 1 through 15 (of 18 total)

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