Is there any feature in SQL Server 2012 or in any version, wherein I can track all DB activities (DDL and DML) and others in one single query with who has modified and from where and when? I need hostname or IP address as well.

I tried below features which has some limitations

  • SQL Audit (Unable to track IP)
  • Event (Unable to track DML operations)
  • Trace (Unable to track DML operations)

I don't want to use triggers.

  • 1
    In a word: No...
    – Philᵀᴹ
    Oct 8 '16 at 13:06
  • Have you looked at EVENTDATA as an option? It might get you some of the way there. msdn.microsoft.com/en-us/library/ms173781.aspx Oct 8 '16 at 17:41
  • Go buy software that does this for you (e.g. Idera Compliance Manager being one). Especially if you are concerned with this for security standard purposes.
    – user507
    Oct 9 '16 at 0:15

I would set up an extended events session to monitor these kind of activities and then review the results of the session from time to time. You can also set up policies that prevent DDL changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.