locked
Auditing for SQL server database RRS feed

  • Question

  • Hi All,

    I have a requirement to capture all the data changes(DML[INSERT,UPDATE,DELETE] & DDL) done by SYSADMIN members with the changes and date for audit purpose.I have googled and came to know that we can create TABLE level triggers for the same,but in our database there are more than 700+ tables are there and creating table level triggers for each of these tables is not a good option.

    Also checked for CDC feature and didn't found it exactly matching to the scenario.

    So can any one advise/suggest  me how to proceed on this


    • Edited by Sandeep777 Tuesday, August 29, 2017 1:45 PM
    Tuesday, August 29, 2017 1:39 PM

All replies

  • What SQL Server version are you using?

    HTH, Regards, Dean Savović, www.comminus.hr, www.savovic.com.hr

    Tuesday, August 29, 2017 1:40 PM
  • Microsoft SQL server 2012
    Tuesday, August 29, 2017 1:47 PM
  • What about DML/DDL changes by non-members of Sysadmin, fixed server role? (ie: db_owner or db_ddladmin or even db_datawriter role members)

    Out of curiosity, why do you need to track changes made by Sysadmin's? Is there suspicion that someone is making unauthorized changes? If so, first consider tightening up security by following the rule of least privileges to reduce risk of unauthorized changes. Meaning, grant ONLY the privileges that a specific user or group needs in order to do their job, and no more.

    CDC is probably you best bet for achieving most of your requirement without spending tons of effort building some kind of custom solution.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, August 29, 2017 2:22 PM
    Tuesday, August 29, 2017 2:21 PM
  • Hi Phil,

    Thanks for the response.

    The thing is that in our organization there is a separate AUDIT team and they need to know the changes done by the DBA team,mostly the data modifications. Thats why we need to implement this.

    Also for CDC after proper research we came to know like there will be some performance issue impact on the server.The server which this audit needs to be implemented is the master server where all SSIS JOBS,replication setups to multiple servers etc is configured,So if we use CDC as an option this will impact the system right?

    CDC we are trying to do a POC to check whether it suits our requirement.But just want to check is there any other solution to achieve this?

    Tuesday, August 29, 2017 2:43 PM
  • Have you considered using the Audit (Database Audit) feature in Sql Server?  See this link which explains how this is set up:  https://mohammaddarab.com/create-database-audit-for-insert-update-delete-in-sql-server/ 

    Tuesday, August 29, 2017 2:48 PM
  • Anything you implement will cost a certain amount of system resources, which could potentially impact performance if the system is already resource constrained.

    As much as CDC might 'cost', I'm sure it would be far less than implementing triggers on 700+ tables.

    Other than CDC, I'm not aware of any 'built-in' feature that will help you accomplish your goal without spending a tremendous amount of time and effort building some custom solution that will most likely end up being high cost and high maintenance.

    There might be some custom solutions that other individuals have developed which you could download and adapt for your purpose. Just do some googling or others might respond here to your question with better ideas than mine.

    For example:
    SQL Server DDL Triggers to Track all Database Changes

    You might look into Auditing feature, but based on my understanding of what you are trying to achieve, I don't think that's what you're looking for.

    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    Tuesday, August 29, 2017 2:54 PM
  • I would suggest you look at a commercial product like this:

    https://www.idera.com/productssolutions/sqlserver/sqlcompliancemanager

    PS.  I have no relation to Idera.

    Tuesday, August 29, 2017 6:59 PM
  • CDC doesn't tell you who did the modification, so if that fact is of importance to you then CDC is a dead end. So is Change Tracking and also temporal tables (if you were on 2016), for the same reasons. The logged data made by a trigger can easily be modified by a sysadmin, and triggers and also be disabled by a sysadmin. Server Audit is probably your best bet, but is has its aspects, like prarmetrizied queries and database audit specifications requires Enterprise Edition in SQL Server 2012.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 30, 2017 6:54 AM
  • Hi Sandeep777,

    >>I have a requirement to capture all the data changes(DML[INSERT,UPDATE,DELETE] & DDL) done by SYSADMIN members with the changes and date for audit purpose.

    If I understand this correctly, I think most of your requirement(well, at least the DML and sysadmin part) is covered in my pervious thread. As for the DDL part, please review the ‘Database-Level audit action groups’ part of this link, and I suppose at least you need to add both ‘DATABASE_OBJECT_CHANGE_GROUP’ and ‘SCHEMA_OBJECT_CHANGE_GROUP’ to audit DDL changes.

    >>Side note on both ‘DATABASE_OBJECT_CHANGE_GROUP’ and ‘SCHEMA_OBJECT_CHANGE_GROUP’

    As you may notice, both group does not allow you to choose principal name, so it will record both sysadmin and non-sysadmin activities. As you are using SQL Server 2012, you can add a filter in your server audit to filter out non-sysadmin activities. Here’s a example:
    USE [master]
    GO
    ALTER SERVER AUDIT [AuditNameHere] 
    WHERE [Database_Principal_Name] = 'dbo'
    GO

    >>Also checked for CDC feature and didn't found it exactly matching to the scenario.

    That might be true, as CDC tracks both DDL command and the value before/after the DDL command, but not the login name. If the goal is only to record sysadmin activities then SQL Server audit is what you need.

    If you have any other questions, please let me know.

    Regards,
    Lin

    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.


    Wednesday, August 30, 2017 7:07 AM
  • Hi Sandeep,

    We had a similar use case and SQL Server Audit worked perfectly for us.

    More details here.

    Hope this helps.

    N

    Wednesday, August 30, 2017 10:24 AM