Trace modifications to SQL objects

• ssghouston

  Newbie

  Points: 5

  More actions

  February 15, 2007 at 5:09 am

  #75129

  In our development environment there are a half dozen SQL programmers that have access to the SQL objects (tables,stored procedures,views/etc).  Is there any way to tell who made changes and the last modified date?  I know it would be difficult to know what was changed, but just knowing that user "jsmith" modified the object on Wed at 11:05am would be very helpful.

• Johan Bijnens

  SSC Guru

  Points: 135591

  More actions

  February 15, 2007 at 5:35 am

  #689907

  - with sql2005 you would use ddl-triggers saving everything you would want to.

  - with sql2000 you could try to use this trace but would not have that much detail as with sql2005. (exported from profiler)

  /****************************************************/

  /* Created by: SQL Server Profiler                  */

  /* Date: 15/02/2007  13:31:37         */

  /****************************************************/

  -- Create a Queue

  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 5

  -- Please replace the text InsertFileNameHere, with an appropriate

  -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

  -- will be appended to the filename automatically. If you are writing from

  -- remote server to local drive, please use UNC path and make sure server has

  -- write access to your network share

  exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

  if (@rc != 0) goto error

  -- Client side File and Table cannot be scripted

  -- Set the events

  -- SQL Server Yukon specific events will not be scripted

  declare @on bit

  set @on = 1

  exec sp_trace_setevent @TraceID, 46, 7, @on

  exec sp_trace_setevent @TraceID, 46, 4, @on

  exec sp_trace_setevent @TraceID, 46, 8, @on

  exec sp_trace_setevent @TraceID, 46, 12, @on

  exec sp_trace_setevent @TraceID, 46, 24, @on

  exec sp_trace_setevent @TraceID, 46, 28, @on

  exec sp_trace_setevent @TraceID, 46, 9, @on

  exec sp_trace_setevent @TraceID, 46, 21, @on

  exec sp_trace_setevent @TraceID, 46, 41, @on

  exec sp_trace_setevent @TraceID, 46, 6, @on

  exec sp_trace_setevent @TraceID, 46, 10, @on

  exec sp_trace_setevent @TraceID, 46, 14, @on

  exec sp_trace_setevent @TraceID, 46, 22, @on

  exec sp_trace_setevent @TraceID, 46, 26, @on

  exec sp_trace_setevent @TraceID, 46, 34, @on

  exec sp_trace_setevent @TraceID, 46, 3, @on

  exec sp_trace_setevent @TraceID, 46, 11, @on

  exec sp_trace_setevent @TraceID, 47, 7, @on

  exec sp_trace_setevent @TraceID, 47, 4, @on

  exec sp_trace_setevent @TraceID, 47, 8, @on

  exec sp_trace_setevent @TraceID, 47, 12, @on

  exec sp_trace_setevent @TraceID, 47, 24, @on

  exec sp_trace_setevent @TraceID, 47, 28, @on

  exec sp_trace_setevent @TraceID, 47, 9, @on

  exec sp_trace_setevent @TraceID, 47, 21, @on

  exec sp_trace_setevent @TraceID, 47, 41, @on

  exec sp_trace_setevent @TraceID, 47, 6, @on

  exec sp_trace_setevent @TraceID, 47, 10, @on

  exec sp_trace_setevent @TraceID, 47, 14, @on

  exec sp_trace_setevent @TraceID, 47, 22, @on

  exec sp_trace_setevent @TraceID, 47, 26, @on

  exec sp_trace_setevent @TraceID, 47, 34, @on

  exec sp_trace_setevent @TraceID, 47, 3, @on

  exec sp_trace_setevent @TraceID, 47, 11, @on

  -- Set the Filters

  declare @intfilter int

  declare @bigintfilter bigint

   

  -- Set the trace status to start

  exec sp_trace_setstatus @TraceID, 1

  -- display trace id for future references

  select TraceID=@TraceID

  goto finish

  error:

  select ErrorCode=@rc

  finish:

  go

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data and code to get the best help
  

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Lowell

  SSC Guru

  Points: 323741

  More actions

  February 15, 2007 at 6:17 am

  #689919

  someone has posted something similar in the script submissions as well:

  http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1584

  Notification of schema changes - Advanced

  Script Rating   Total number of votes [2]

  By: Brian Corrigan

  This script modifies another excellent script written by SHAS3. The original script examines tables for changes and sends an email. This modification examines all stored procedures, tables, indexes, etc. in ALL databases or just the CURRENT database. If changes are detected, the script has the option to either email or log to a table that it will create for you. In addition, several performance enhancements are included as well as NOLOCK statements where appropriate to make it production ready.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!