SQL Server: How to trace down database activities?

Create trigger to log activities

• Record actions happening on CREATE/ALTER/DELETE stored proc
  Create table to save actions happening

  [sql]
  CREATE TABLE dbo.ProcedureChanges
  (
  EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  EventType VARCHAR(100),
  EventDDL VARCHAR(MAX),
  DatabaseName VARCHAR(255),
  SchemaName VARCHAR(255),
  ObjectName VARCHAR(255),
  HostName VARCHAR(255),
  IPAddress VARCHAR(32),
  ProgramName VARCHAR(255),
  LoginName VARCHAR(255)
  );
  [/sql]

  Save initial content of stored procedures, functions

  [sql]
  INSERT ProcedureChanges
  (
  EventType,
  EventDDL,
  DatabaseName,
  SchemaName,
  ObjectName
  )
  SELECT
  N’Initial control’,
  ROUTINE_DEFINITION,
  ROUTINE_CATALOG,
  SPECIFIC_SCHEMA,
  ROUTINE_NAME
  FROM
  INFORMATION_SCHEMA.ROUTINES
  [/sql]
  or
  [sql]
  INSERT Utility.dbo.ProcedureChanges
  (
  EventType,
  EventDDL,
  DatabaseName,
  SchemaName,
  ObjectName
  )
  SELECT
  N’Initial control’,
  OBJECT_DEFINITION([object_id]),
  DB_NAME(),
  OBJECT_SCHEMA_NAME([object_id]),
  OBJECT_NAME([object_id])
  FROM
  sys.procedures;
  [/sql]

  Create trigger to record actions relating to Stored Procedure

  [sql]
  CREATE TRIGGER CaptureStoredProcedureChanges
  ON DATABASE
  FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
  AS
  BEGIN
  SET NOCOUNT ON;

  DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

  SELECT @ip = client_net_address
  FROM sys.dm_exec_connections
  WHERE session_id = @@SPID;

  INSERT ProcedureChanges
  (
  EventType,
  EventDDL,
  SchemaName,
  ObjectName,
  DatabaseName,
  HostName,
  IPAddress,
  ProgramName,
  LoginName
  )
  SELECT
  @EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘VARCHAR(100)’),
  @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘VARCHAR(MAX)’),
  @EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘VARCHAR(255)’),
  @EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘VARCHAR(255)’),
  DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
  END
  [/sql]

  You can create trigger to record actions on function as well

  [sql]
  CREATE TRIGGER CaptureFunctionChanges
  ON DATABASE
  FOR CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION
  AS
  BEGIN
  SET NOCOUNT ON;

  DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);

  SELECT @ip = client_net_address
  FROM sys.dm_exec_connections
  WHERE session_id = @@SPID

  INSERT ProcedureChanges
  (
  EventType,
  EventDDL,
  SchemaName,
  ObjectName,
  DatabaseName,
  HostName,
  IPAddress,
  ProgramName,
  LoginName
  )
  SELECT
  @EventData.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘VARCHAR(100)’),
  @EventData.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘VARCHAR(MAX)’),
  @EventData.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘VARCHAR(255)’),
  @EventData.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘VARCHAR(255)’),
  DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
  END[/sql]

• DDL Trigger list
  http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes
• How to list up DB triggers

  [sql]select * from sys.triggers order by create_date desc[/sql]