Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Investigating deletes from a table Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 4:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:10 AM
Points: 2, Visits: 43
Hi! This is my first post here, but I'm a longtime reader.
I'm investigating who or what is deleting records from a table. I've used some scripts to query the TLogs to find the login that executed the delete statements. I found that it is our general purpose login that we let applications connect with. So we have several possible culprits, all using the same login.

Is there a way to find out:
1. What specific application using the general purpose login ran the deletes?
2. What specific data was deleted?

Thanks in advance!
Post #1568232
Posted Wednesday, May 7, 2014 1:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,533, Visits: 7,101
You can use a delete trigger, output the deleted into a table. Easy to use any of the security functions (CURRENT_USER, ORIGINAL_LOGIN(), SESSION_USER, USER, USER_NAME(), SYSTEM_USER) as a column default in the log table.
Post #1568313
Posted Wednesday, May 7, 2014 2:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:28 AM
Points: 579, Visits: 914
Try to create a DDL trigger for the same.
Follw below link.
DDL Triggers


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1568321
Posted Wednesday, May 7, 2014 11:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:10 AM
Points: 2, Visits: 43
Thank you...we got it working.
Post #1568632
Posted Wednesday, May 7, 2014 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
daniel.harper (5/7/2014)
Thank you...we got it working.


Can you share your solution so that others may benefit from your hard work? People will find this thread in the future and will be very grateful to find a solution along with the question.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568650
Posted Thursday, June 26, 2014 10:55 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 PM
Points: 459, Visits: 1,065
I would be interested in seeing the solution.
Wouldn't "SELECT CURRENT_USER, ORIGINAL_LOGIN(), SESSION_USER, USER, USER_NAME(), SYSTEM_USER" return the general user account ? If this is a scheduled job created long ago, who would be the original login ? Just curious.

Post #1586569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse