Tracking users in the CDC for SQL Server 2008

Posted on 2011-10-07
Last Modified: 2012-05-12
Per the following article highlights using the CDC to keep track of changes in tables for SQL Server but I don't see where it records the Windows username per each change?  That would seem to be the most important value to record in amongst the changes.  Am I just missing that or is that not possible to do entirely in SQL Server with the CDC?
Question by:stephenlecomptejr
    LVL 39

    Accepted Solution

    If you implement SQL DDL triggers:

    using Using the EVENTDATA Function

    this has a CURRENT_USER info about "The database user against whose session the event occurred"

    Also please keep in mind the SQL has a Standard Report "Schema Change History" just right click the database in SSMS, go to Reports and select Standard the one I mentioned. Thias report keeps data until SQL service restart.
    LVL 39

    Expert Comment

    Here's how my DDL triggers look like and they have user info as you mentioned - this IS the point to know who/when changed something in the DB:

    CREATE TRIGGER [MyDB_ddl_events]
    DECLARE @data XML
    SET @data = EVENTDATA()
    if @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')  
                NOT      IN (
          and (@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)')) <> 'NT AUTHORITY\SYSTEM:dbo'
    INSERT MyDBAudit.dbo.dba_ddl_audit (posted_on, db_user, event_logged, transact_sql,DatabaseName,SchemaName,ObjectName,ObjectType)
       VALUES       (--GETDATE(),
                      @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
                      @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') ,
                      @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
                      'database - ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') + ': ' + @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
                      @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
                      @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'),
                      @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
                      @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(50)') );


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now