locked
Step by step to auditing all querries generated by members of an admin group RRS feed

  • Question

  • I have searched and read a multitude of articles regarding this topic and yet I have not grown any wiser.

    Can someone please inform me how to achieve this goal:

    ► We have a group of sysadmins represented by a domain group - let us name the group in this example as GROUPadmins

    ► We have recently been informed that we need to track and audit all queries generated by this group of admins.

    ► I have no idea where to start.

    Anyone out there has a guide to a recipe to follow on achieving this goal.

    Our SQL servers are mostly running Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)  (we have some 2012 and some 2014).

    Kind Regards

    Akrofly

    Wednesday, November 21, 2018 3:17 PM

Answers

  • Hi Akrofly,

     

    You can create a server audit containing a WHERE clause via the T_SQL or you can also enter a predicate on the Create Audit Filter page to filter the audit logs.

     

    The field in the link can be filtered except file_name, audit_file_offset, and event_time.

     

    For example, there are just the server_principal_name is not 'Adv' and the application_name is 'Microsoft SQL Server Management Studio - Query' can be logged in the Audit records.

     

     

     

    Best Regards,

    Emily


    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 Akrofly Thursday, December 13, 2018 7:34 PM
    Friday, November 23, 2018 7:59 AM

All replies

  • https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

    Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 22, 2018 7:22 AM
  • Hi Akrofly,

     

    Firstly, you need to create an audit specification( Expand the Security folder in Object Explorer and right click the Audits folder then click New Audit.).

     

     

     

    In Create Audit Dialog, please specify these options.

     

     

     

    Next you can create Server Audit Specifications or Database Audit Specifications(Right click the Server Audit Specifications or Database Audit Specifications folder and click New Server Audit Specifications or New Database Audit Specifications). In Audit box, please select the audit specification you created in the first step.

     

    From your description, I knew that you want to audit all queries generated by this group of admins. But I am not very clear about the specific operation details that you want to audit. You can refer to the SQL Server Audit Action Groups and Actions to decide the Audit Action Type. As for Principal Name, please select the "dbo".

     

    For example:

     

     

     

    After creating the Server Audit Specifications or Database Audit Specifications, please remember to enable them.

     

    For detailed steps, please refer to the blog: https://solutioncenter.apexsql.com/how-to-setup-and-use-sql-server-audit-feature/

     

    Best Regards,

    Emily


    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

    • Proposed as answer by Bob_FT Sunday, November 25, 2018 5:13 PM
    Thursday, November 22, 2018 8:22 AM
  • Thank you for a well written and very explanatory description.

    As for the audit actions, they would be on a DB level:

    SELECT

    UPDATE

    INSERT

    DELETE

    The challenge here is that we have many "older" applications that connect to databases as dbo, one of them generating millions of interactions daily.

    Is there a way to isolate certain dbo's ? Any way around that?

    Friday, November 23, 2018 3:18 AM
  • Hi Akrofly,

     

    You can create a server audit containing a WHERE clause via the T_SQL or you can also enter a predicate on the Create Audit Filter page to filter the audit logs.

     

    The field in the link can be filtered except file_name, audit_file_offset, and event_time.

     

    For example, there are just the server_principal_name is not 'Adv' and the application_name is 'Microsoft SQL Server Management Studio - Query' can be logged in the Audit records.

     

     

     

    Best Regards,

    Emily


    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 Akrofly Thursday, December 13, 2018 7:34 PM
    Friday, November 23, 2018 7:59 AM