Validate SQL Server 2005 Changes
You can set up a profiler trace to capture the Audit Object Management event and filter based on the name of the database you care about. Every time an object in the schema is created, dropped, edited, it fires an event in the profiler that includes the person who made the change and the name of the stored procedure.
You will need at least these profiler columns: ApplicationName - the username of the application started when the DatabaseName changed - the Databse containing the object changed by EventSubClass - The action type shows Alter, Modify, Drop, Create, etc. LoginName - Change User ObjectName - Affected Object
source to share
[last but adds details on how to see who made changes even before the audit system is implemented]
Others have already looked at various ways to run data audits to track future changes, but if you didn't have any kind of auditing system in the first place, then it is very difficult to figure out who did what and when historically.
The only option is to try to read the transaction log, assuming the database is in full recovery mode. The problem is that this is not supported by default. Possible options:
- Third party tools like ApexSQL Log or Quest Toad
- Undocumented features like DBCC LOG or fn_dblog
See these sections for details:
Viewing the Transaction Log in SQL Server 2008
source to share
I agree. This could be a SQL Server Profiler with filters. DDL triggers existed in SQL Server. You can create something like this:
CREATE TRIGGER ddl_drop_procedure
ON DATABASE
FOR DROP_PROCEDURE
AS
RAISERROR ('You deleted a stored procedure',10, 1)
GO
Another option is to use third party tools like Auto Audit from codeplex or apexSQL trigger.
source to share