none
Audit Trail Tables RRS feed

  • Question

  • We have been asked to add audit trails to about 20 tables in our sql 2016 database to be able to identify who inserted, changed or deleted data in each table.  I have seen solutions that use a single table to identify table, column, old data, new data, etc.  I was thinking that it would be easier to simply create 20 audit tables, one for each table to track, and then simply write out the old row prior to change and include username and datetime to identify who made the change.  I would include in a trigger the insert to this audit table(s) when a row was inserted, changed or deleted.  The database is not huge, maybe 1-2 GB in size so I am not concerned with growth that much. Any ideas on this?
    Friday, October 11, 2019 1:05 PM

All replies

  • Friday, October 11, 2019 2:21 PM
    Moderator
  • Thanks.  I don't think I can use CDC as we are using a generic login to the sql server and not the actual user.  In our web app we have the login name/identifier.
    Friday, October 11, 2019 2:27 PM
  • You can still use CDC or the triggers.  

    If you use the same login for everyone, you will never know the actual user unless you store it in a column in the database.  That is not a problem to solve with tracing.

    Friday, October 11, 2019 2:33 PM
    Moderator
  • Hi David,

    You can Use OUTPUT Clause or TRIGGER.

    If we use OUTPUT Clause, then need to do respective changes in respective sp's and other existing codes (having huge impacts).

    The other Choice TRIGGER suits Fine in your case that don't have much impacts.


    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Friday, October 11, 2019 2:41 PM
  • My plan is to use triggers.  The biggest question I have is how to get the users id number into the trigger.  We have thousands of users and we have their credentials checked and store the ID number (which is meaningless to anyone else) in a secure cookie.  We can pass that cookie value to our stored procedure but I don't know if a stored procedure variable is accessible in a trigger.
    Friday, October 11, 2019 3:47 PM
  • Hi David,

    Try the below link for Pass a variable into a trigger-

    https://stackoverflow.com/questions/2646547/pass-a-variable-into-a-trigger

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Friday, October 11, 2019 3:55 PM
  • Although you can use SET CONTEXT_INFO to pass some information to triggers, I find it much more useful to add a column to the table "last_changed_by" and set it to the actual user from the web app on insert/update.  On deletes, just update the changed_by, and then delete the row.  That way you have a record of who deleted the record. 

    Then your audit trail will just work, regardless of method used.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-context-info-transact-sql?view=sql-server-2017
    Friday, October 11, 2019 5:30 PM
    Moderator
  • I think that might work.  I was hoping to not have to add a column to all 15 tables as I was only planning to have it in the Audit tables..  Also, all existing rows will be missing the user but probably not a concern.  I have to add the user variable to all stored procedures for insert, update and delete for all 15 tables and all places in the web application where those stored procedures are called.
    Friday, October 11, 2019 5:46 PM
  • My plan is to use triggers.  The biggest question I have is how to get the users id number into the trigger.  We have thousands of users and we have their credentials checked and store the ID number (which is meaningless to anyone else) in a secure cookie.  We can pass that cookie value to our stored procedure but I don't know if a stored procedure variable is accessible in a trigger.

    The best is to do this on connection:

    EXEC sp_set_session_context 'USERID', @idnumber, @read_only = 1

    @read_only prevents that the user can tamper the value trough any SQL injection hole you may have.

    Then in the triggers you do

    SELECT @idnumber = convert(int, session_context(N'USERID'))

    This is the same idea that Tom discussed, but he mentioned SET CONTEXT_INFO which is an older and more primitive method. Context_info only gives you 128 bytes to play with. Session context a lot more, and you can have different values.

    By the way, Tom also suggested CDC. CDC is not a solution for auditing as it only track what was changed, but not by whom.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 11, 2019 9:16 PM
    Moderator
  • By the way, Tom also suggested CDC. CDC is not a solution for auditing as it only track what was changed, but not by whom.



    That is what I thought when I looked at CDC but have no experience with it.  Your solution seems cleaner so I think I will give it a try.  Thank you for your help.
    Friday, October 11, 2019 9:28 PM
  • Hi David,

     

    Have you solved this problem ?  In order to close this thread, please kindly mark your replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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

    Monday, October 14, 2019 8:21 AM
  • You are going to have to modify every access to the database in the web app to pass the user to every query either way you choose to implement the audit. 

    Monday, October 14, 2019 11:57 AM
    Moderator
  • You are going to have to modify every access to the database in the web app to pass the user to every query either way you choose to implement the audit. 

    Not sure what you have in mind here, but if David's team already have a common method to set up a connection, they can issue a call to sp_set_session_context from this place.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 14, 2019 12:54 PM
    Moderator
  • Should I assume that this call to sp_set_session_context will need to be in each stored procedure we call?
    Monday, October 14, 2019 1:20 PM
  • Should I assume that this call to sp_set_session_context will need to be in each stored procedure we call?
    As I suggested, the best if you can submit it from a common place in your application. Having it in each stored procedure will not be reliable - you will forget it half of the time.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 14, 2019 3:25 PM
    Moderator
  • So it will hold for a user even though they go in and out of the application all day long?
    Monday, October 14, 2019 4:46 PM
  • As Erland said, it is common to have a standard query function in your code where you might be able to stick the set_context_info with the user name, like execute_query(querystring).  

    If you don't have that, using dapper or something else directly querying the database, you would need to modify every place you call the database to add the set_context_info before executing the query.  Or I would suggest you implement a standard function if you can.  

    I find it easier to have the last modified user and date/time in the actual table.  I just do that as a standard practice for user changeable data.  I always regret when I don't do it to begin with.

    Monday, October 14, 2019 5:25 PM
    Moderator
  • I was thinking of doing it when the user logs into the application and is validated.  Was not sure if a query that user runs 2 hours later will hold that set_context_info value.  This is a web app.
    Monday, October 14, 2019 5:44 PM
  • You will not be able to do that if you are using connection pooling.  The connection does not "belong" to a user.  A connection is reused for all database commands from the web server for all users.  You will need to set it on every call to the database.

    Monday, October 14, 2019 6:21 PM
    Moderator
  • I was thinking of doing it when the user logs into the application and is validated.  Was not sure if a query that user runs 2 hours later will hold that set_context_info value.  This is a web app.

    That depends on the architecture of the application, but it is not very common that an application opens a connection and keeps it open. And it is even less common in a web application. Rather the pattern is that a method that runs a query, opens a connection, runs a query, and disconnects logically. The client API then clings to the physical connection under the covers and reuses it, but that is irrelevant here.

    What we have been saying is that every time you connect, you need to submit sp_set_session_context. If the application is written so that the connection is opened directly with SqlConnection.Open(), this means that there is a lot of changes you need to make, and this is not viable. But it is not uncommon that the applications have a common routine for opening connections already, and in such case it may be just a single spot you need to change.

    If you are an SQL guy who don't to application development yourself, you should take with your application team, and possibly invite them to this thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 14, 2019 9:37 PM
    Moderator