[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Tracking users in the CDC for SQL Server 2008

Posted on 2011-10-07
2
Medium Priority
?
658 Views
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?

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/
0
Comment
Question by:stephenlecomptejr
  • 2
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 36932601
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36932620
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

872 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