none
Does a way exist to audit all data changes for a specific user?

    Question

  • Hello -

    Does a way exist to audit all data changes for a specific user?  For example, if we have a user that only performs some ad-hoc data changes in a database, can we easily audit what that user did to the data?

    Thank you in advance!

    Thursday, May 23, 2013 6:43 PM

Answers

  • One option is SQL Server Audit, requires SQL 2008 and Enterprise Edition. Setting up an audit specification is fairly simple. And if the user only runs queries through a query window, not through stored procedures or an application, you can also see the values - as long as he does not cheat and start to use variables in his statements.

    Another option is to set up a trace and filter for this specific user.

    Then you can go the trigger route, but that is more work.

    What kind of user is this? A physical person that runs suspect queries on his own? Or is it an application user which is the proxy for a bunch of real persons?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 23, 2013 9:32 PM

All replies

  • One option is SQL Server Audit, requires SQL 2008 and Enterprise Edition. Setting up an audit specification is fairly simple. And if the user only runs queries through a query window, not through stored procedures or an application, you can also see the values - as long as he does not cheat and start to use variables in his statements.

    Another option is to set up a trace and filter for this specific user.

    Then you can go the trigger route, but that is more work.

    What kind of user is this? A physical person that runs suspect queries on his own? Or is it an application user which is the proxy for a bunch of real persons?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 23, 2013 9:32 PM
  • You can set some policies on that particular user if he apply any changes in your file server it will give you an alert for the same 

    For this most companies using File server Auditing software in their network to track all these activities in their network also some vendor provide full package of auditing like SQL, Exchange, Active Directory, File Server also .


    Tuesday, May 28, 2013 1:22 PM