SQL Server 2005 Change Auditing

Question

Is there a built in way in SQL Server 2005 to audit things specifically like deleting a stored procedure? Is there a history table that I can query? We have a mystery sproc that has disappeared a few times now.

Answer 1

You can build this using DDL triggers:

http://msdn.microsoft.com/en-us/library/ms190989.aspx

Answer 2

Only if you use DDL triggers or use profiler to trace text "%DROP%PROC%procname%"

Answer 3

You can setup a profiler trace to capture the Audit Schema Object Management event and filter based on the database name you care about. Any time an object in the schema is created, dropped, edited it will fire an event in profiler that includes the person who did the change and the name of the stored procedure.

You will want at least these profiler columns: ApplicationName - name of app user was running when they made change DatabaseName - Databse containing the object changed EventSubClass - Type of action shows Alter, Modify, Drop, Create etc LoginName - user making change ObjectName - object affected

Answer 4

Note that in SQL Server 2008 they also now have AUDIT to replace Profiler Traces for auding activities. It is similar but has its own configuration UI and UI to view results