Tracking users in the CDC for SQL Server 2008

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?

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/
LVL 1
stephenlecomptejrAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
If you implement SQL DDL triggers:

http://msdn.microsoft.com/en-us/library/ms186406(v=sql.90).aspx

using Using the EVENTDATA Function

http://msdn.microsoft.com/en-us/library/ms187909(v=sql.90).aspx

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.
0
 
lcohanDatabase AnalystCommented:
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]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
if @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')  
            NOT      IN (
                              'ALTER_AUTHORIZATION_DATABASE'
                              ,'ALTER_USER'
                              ,'CREATE_ROLE'
                              ,'CREATE_SCHEMA'
                              ,'CREATE_USER'
                              ,'DENY_DATABASE'
                              ,'DROP_ROLE'
                              ,'DROP_SCHEMA'
                              ,'DROP_USER'
                              ,'GRANT_DATABASE'
                              ,'REVOKE_DATABASE'
                              ,'UPDATE_STATISTICS'
                        )
      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)') );


GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.