locked
how to audit who connected to my database RRS feed

  • Question

  • in SQL Server 2008R2, how to audit who connected to one of my database?

    Thanks


    Thank you Skiiiiii

    Tuesday, November 27, 2018 12:21 AM

Answers

  • 1) Server side tracing using profiler. You should select only the required counters. I would select spid, loginname,starttime and filter it to the database name.

    2) Extended events - select what is required. Should be same as trace counters.

    3) Logon triggers 

    Regards;
    Vivek Janakiraman

    • Proposed as answer by Teige Gao Tuesday, November 27, 2018 1:43 AM
    • Marked as answer by Skiiiiii Wednesday, November 28, 2018 12:05 AM
    Tuesday, November 27, 2018 12:45 AM
  • Hi Skiiiiii,

    I will use the extended events, extended events is lighter than Profiler and will not affect the performance much.

    We can select the session in extended events and set the filter.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Skiiiiii Wednesday, November 28, 2018 12:05 AM
    Tuesday, November 27, 2018 2:21 AM

All replies

  • It is SQL Server Standard Edition. Cannot create audit. any idea can we use trigger or other method?

    Thank you Skiiiiii

    Tuesday, November 27, 2018 12:22 AM
  • 1) Server side tracing using profiler. You should select only the required counters. I would select spid, loginname,starttime and filter it to the database name.

    2) Extended events - select what is required. Should be same as trace counters.

    3) Logon triggers 

    Regards;
    Vivek Janakiraman

    • Proposed as answer by Teige Gao Tuesday, November 27, 2018 1:43 AM
    • Marked as answer by Skiiiiii Wednesday, November 28, 2018 12:05 AM
    Tuesday, November 27, 2018 12:45 AM
  • Hi Skiiiiii,

    I will use the extended events, extended events is lighter than Profiler and will not affect the performance much.

    We can select the session in extended events and set the filter.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Skiiiiii Wednesday, November 28, 2018 12:05 AM
    Tuesday, November 27, 2018 2:21 AM