Open Forum

Expand all | Collapse all

SQL Question: ability to log user activity?

  • 1.  SQL Question: ability to log user activity?

    GOLD CONTRIBUTOR
    Posted Feb 26, 2019 04:58 PM
    Thanks NAVUG Forum for being a place where we can ask the larger community of smart people questions barely related to NAV. :-)

    This is a question for someone who is good with SQL. We have some users who access NAV SQL directly through queries. So basically the query itself has nothing to do with NAV the application. Do you know if or how we can get SQL to log the user when they access the database directly? We want to be able to track who did what when.

    Note: we know there might be some short term options with Profiler but we don't want to go that route.

    Thanks.

    ------------------------------
    Andrea Hall
    Information Technology Director
    Stonewall Kitchen
    York ME
    ------------------------------


  • 2.  RE: SQL Question: ability to log user activity?

    GOLD CONTRIBUTOR
    Posted Feb 27, 2019 08:20 AM
    Edited by Mathew Ealy Feb 27, 2019 08:20 AM
    It would depend on how the users are accessing the queries, if they are running a pre-built report using SSRS and a web portal the reports have to be written in a specific way to capture the activity, if it's a query being written as a one off just at the time of extraction, I think it stores a lot of that information in sys.dm_exec_query_stats table.
    Here's a stack exchange post about writing a report to pull that information.
    https://dba.stackexchange.com/questions/46009/get-username-and-or-ip-address-responsible-for-a-query

    ------------------------------
    Mathew Ealy
    Nav Support Specialist
    Rentokil N. America
    Reading PA
    ------------------------------



  • 3.  RE: SQL Question: ability to log user activity?

    Posted Mar 05, 2019 02:57 PM
    Maybe check out a 3rd party SQL Auditing Tool like ManageEngine SQL Database Auditing.

    ------------------------------
    Alex Scheler
    Carma Laboratories, Inc.
    Franklin WI
    ------------------------------



  • 4.  RE: SQL Question: ability to log user activity?

    TOP CONTRIBUTOR
    Posted Mar 05, 2019 06:07 PM
    ​Hi Andrea,

    I think FastPath can audit changes made to the NAV database that are made through NAV or through SQL.




    ------------------------------
    Lewis Rosenberg
    IT Manager
    Mars Fishcare
    Chalfont PA
    -----------------------------------------------
    BCUG/NAVUG All-Star
    BCUG/NAVUG Board of Advisors, Chairperson
    BCUG/NAVUG Programming Committee

    NAVUG/BCUG FOCUS Houston March 13-14, 2019
    Join "SuperAdmins To The Rescue" March 13 at 10:15am
    Why attend Focus? Video Blog: https://www.navug.com/blogs/lewis-rosenberg/2019/02/25/time-for-focus


    Twitter: @RosenbergL
    LinkedIn: https://www.linkedin.com/in/rosenbergl
    ------------------------------



  • 5.  RE: SQL Question: ability to log user activity?

    TOP CONTRIBUTOR
    Posted Mar 08, 2019 01:40 AM
    I may be late to the party but you can use SQL Profiler to track specific user including their queries or tables they may be accessing.

    ------------------------------
    Kristoffer Ruyeras
    Tigunia
    Seattle, WA
    ------------------------------



  • 6.  RE: SQL Question: ability to log user activity?

    TOP CONTRIBUTOR
    Posted Mar 11, 2019 08:16 AM
    Thanks everyone for the responses!

    There is an object in SQL Server called SQL Server Audit.  Anyone ever used this?  We were hoping for an out of the box solution and one that doesn't require us to run sql profiler since this will be something we have turned on all the time.  Here is a link about what I am talking about:
    https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017


    ------------------------------
    Jason Wilder
    Senior Application Developer
    Stonewall Kitchen
    York ME
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role