locked
trigger - who deleted what RRS feed

  • Question

  • in the executed statement of the trigger how can I show who ran the query and is there an easy way to change the deleted data in to a string?

    basically I just want to have the trigger go off on deletion and show me who did it and what they deleted. figured this would be on the web but I havent found a good example of grabbing this info.

    Thanks

    Tuesday, May 3, 2011 6:56 PM

Answers

  • Hi,

    This should be relatively easy...

    Did you try using functions like USER_NAME(), CURRENT_USER() or SESSION_USER() in the trigger to get the login/user details for the person 'doing the deletion'?

    You should be able to query the INSERTED and DELETED system table in the trigger to get information on what was changed?

    Regards,

    Gary.

     

    MCP, MCDBA, MCITP Database Administrator

     

     


    Gary Wells
    • Proposed as answer by Naomi N Tuesday, May 3, 2011 7:34 PM
    • Marked as answer by jrich Tuesday, May 3, 2011 8:41 PM
    Tuesday, May 3, 2011 7:13 PM
  • The simplest way is to write the information into an Audit table with the information about who deleted the item, when and what item was deleted.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by jrich Tuesday, May 3, 2011 8:42 PM
    Tuesday, May 3, 2011 7:46 PM
  • Try SUSER_NAME() instead.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by jrich Wednesday, May 4, 2011 7:32 PM
    Wednesday, May 4, 2011 7:21 PM
  • No or not that I know of from the top of my head. How should SERVER application know about Client ID ?

    Although google knows everything

    http://www.dbforums.com/microsoft-sql-server/1623654-how-get-client-ip-address-t-sql.html

    So, try HOST_NAME() function for now.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Marked as answer by jrich Wednesday, May 4, 2011 7:33 PM
    Wednesday, May 4, 2011 7:26 PM

All replies

  • Hi,

    This should be relatively easy...

    Did you try using functions like USER_NAME(), CURRENT_USER() or SESSION_USER() in the trigger to get the login/user details for the person 'doing the deletion'?

    You should be able to query the INSERTED and DELETED system table in the trigger to get information on what was changed?

    Regards,

    Gary.

     

    MCP, MCDBA, MCITP Database Administrator

     

     


    Gary Wells
    • Proposed as answer by Naomi N Tuesday, May 3, 2011 7:34 PM
    • Marked as answer by jrich Tuesday, May 3, 2011 8:41 PM
    Tuesday, May 3, 2011 7:13 PM
  • those functions sound like they'll get me the user info, I'll play with those.

    I did play with the inserted/deleted tables, but I was wondering if there was an easy way to take multiple fields and change it to a string so I could email it.

    I guess for the most part I only care about the id (int) and name (nvarchar(30))

    my tsql is pretty weak, typically do my coding on the front end app with really basic tsql statement :-/

    also, in case there are a lot of triggers (shouldn’t be) what is the preferred method for dumping the info? log file? event log? I can use powershell or something else to pull what I need once a day or whaterver, mostly just curious where I should have the trigger dump the info (easiest/best practice)

    Thanks again

    Tuesday, May 3, 2011 7:25 PM
  • The simplest way will be to store the information in the Audit table. You can google on 'Audit SQL Server'. Alternatively, if you're using Enterprise edition of SQL Server, you can use Change Data Capture.

    This blog is a good introduction to CDC and let me know if you need more comprehensive blogs on CDC as well:


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, May 3, 2011 7:37 PM
  • I guess I should probably detail what im trying to accomplish...

    we have a problem with users deleting macro's from the app. we want to know who is deleting them so I was going to set a trigger on the table (autotext) to monitor for deletes and see if I could pinpoint who was doing it and figure out what macro's (name) they were deleting. we have a pretty good idea who is doing it, but he isnt the sort of guy we can just remove access from to get him to stop doing it...

    its SQL 05 enterprise.

    DBA is like 5th or 6th on my "its your responsibility" list so I trend to just google whatever it is I need to do... just not having much luck with google and this task.

    I'll look in to those two items.

    Thanks again

    Tuesday, May 3, 2011 7:43 PM
  • The simplest way is to write the information into an Audit table with the information about who deleted the item, when and what item was deleted.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by jrich Tuesday, May 3, 2011 8:42 PM
    Tuesday, May 3, 2011 7:46 PM
  • so basically just use the trigger on deleted, dump the data from the auto generated deleted table in there as well as whatever else I can pull.

    I think I once saw a nifty way to nest a select in to an insert but if I recall correctly it was a one to one, is there a good way to do the insert with a nested select plus add some info?

    insert into table_audit (user, dt, f1,f2,f3) VALUES (user_name(), getdate(), (select f1,f2,f3 from deleted))

    im guessing that wont work, but is there a way to do something like that?

    Thanks

    Tuesday, May 3, 2011 7:56 PM
  • insert into AuditTable (itemDeleted, WhoDeleted, WhenDeleted)
    
    select D.Item, User_Name(), CURRENT_TIMESTAMP from Deleted D
    

    This is the statement you can use to log the info.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    Tuesday, May 3, 2011 8:01 PM
  • so I got the trigger working as I'd like, but the user_name() always returns dbo... I did a trace on a test system, the LoginName is sa (sweet app) and there is no NT username... so, is there some other piece of info I could grab some way? such as the client IP? if I could pinpoint the IP of the machine I could track it back to a user...

    I played with the other options (current_user, session_user) and those were dbo as well.

    Thanks

    Wednesday, May 4, 2011 6:30 PM
  • I am afraid you may be out of luck here. How did you test your trigger? If, say, you're running changes from SSMS, what do you see as a name?

    Then, when you try from the application, what do you see?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, May 4, 2011 6:36 PM
  • alter TRIGGER tr_Source_Delete
    ON AutoText
    FOR DELETE
    AS
    BEGIN
    insert into Audit_AutoText(uname, name, descript) select user_name(), D.name, D.description from deleted D
    END
     so it creates the entries ok, when run from the application (.net sqlclient) but, as I said, the uname comes back as DBO... I tried doing some testing with in SSMS (windows auth) on temp tables and it also showed DBO... I tried it from powershell (invoke-sqlcmd -query) and again, DBO...

    select user_name(), current_user, session_user

    returns DBO for all 3..

    Thanks

    Wednesday, May 4, 2011 7:11 PM
  • Try SUSER_NAME() instead.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by jrich Wednesday, May 4, 2011 7:32 PM
    Wednesday, May 4, 2011 7:21 PM
  • that works great, problem is that because they set it up to just use the SA account, all I get is SA... is there a function that will pull the client IP?

    Wednesday, May 4, 2011 7:24 PM
  • No or not that I know of from the top of my head. How should SERVER application know about Client ID ?

    Although google knows everything

    http://www.dbforums.com/microsoft-sql-server/1623654-how-get-client-ip-address-t-sql.html

    So, try HOST_NAME() function for now.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Marked as answer by jrich Wednesday, May 4, 2011 7:33 PM
    Wednesday, May 4, 2011 7:26 PM
  • well, sql accepts the connection, as it accepts the login/user info... figured there might be a way.. not to mention if you run activity monitor it is able to grab the host name of the connection... each connection has a session id.... figured there would be some chain that would get me there...

    but, never mind.... im focusing too much on SQL and completely forgot that this needs to be done through the web interface, provided I have the time in which the delete happened I can correlate it to the IIS logs to pin down a user/machine.

    I really appreciate your help and responsiveness with this, you've been great!

    Wednesday, May 4, 2011 7:31 PM