SQL Server: Find Who Modified a Stored Procedure

Stored procedures are a critical part of many SQL Server applications, often containing business logic, data transformations, and performance-optimized queries. Because of their importance, even a small modification to a stored procedure can impact application behavior, reporting accuracy, or system performance.

A common challenge for database administrators and support teams arises when a stored procedure behaves differently than expected, one of the first questions asked is:

• Who changed the stored procedure?
• When was it changed?
• From which client machine was the change made?

While SQL Server records when an object was last modified, it does not automatically provide a complete audit trail of who made the change and where it originated. To answer these questions, organizations must rely on auditing features, monitoring tools, or custom tracking mechanisms.

In this article, we'll explore the approaches to identifying stored procedure modifications in SQL Server, including SQL Server Audit, Extended Events, and DDL Triggers. We'll also discuss the advantages, limitations, and performance considerations of each method to help you choose the right solution for your environment.

Why Tracking Stored Procedure Changes Matters

Stored procedures often contain critical business logic that directly affects application functionality, data processing, reporting, and system performance. When a stored procedure is modified without proper visibility, troubleshooting issues can become time-consuming and costly.

Tracking stored procedure changes helps database administrators and development teams answer important questions such as:

• Who made the change?
• When was the change made?
• What was modified?
• Which machine or application initiated the change?

Faster Troubleshooting

When an application suddenly starts behaving differently, a recent stored procedure modification is often one of the first areas to investigate. Having an audit trail allows teams to quickly identify whether a change occurred and who performed it.

Improved Security and Compliance

Many organizations must comply with internal governance policies or industry regulations that require monitoring changes to database objects. Tracking stored procedure modifications helps establish accountability and supports audit requirements.

Change Management and Accountability

In environments where multiple developers, DBAs, and support engineers have access to production systems, maintaining a record of changes ensures that modifications can be traced back to the responsible individual or team.

Preventing Unauthorized Changes

Without auditing, unauthorized or accidental modifications may go unnoticed until they cause application failures or data inconsistencies. Monitoring stored procedure changes helps detect such activities early.

Easier Root Cause Analysis

When performance issues or data discrepancies occur, knowing exactly when a stored procedure was modified can significantly reduce the time required to identify the root cause.

Better Operational Visibility

By capturing information such as login name, host machine, timestamp, and executed commands, organizations gain greater visibility into database activities and can maintain a more controlled production environment.

For these reasons, implementing a reliable mechanism to track stored procedure changes is considered a best practice for SQL Server environments, especially in production systems where stability, security, and accountability are essential.

Check When a Stored Procedure Was Modified

Before investigating who changed a stored procedure, it's helpful to determine when the modification occurred. SQL Server maintains metadata for database objects, including the creation date and the last modification date.

You can retrieve this information using the sys.procedures system catalog view:

SELECT name, create_date, modify_date FROM sys.procedures WHERE name = 'YourProcedureName';

Example output:

name				create_date			modify_date
------------------------------------------------------------
usp_GetSalesReport	2021-03-15 10:25:18	2025-06-02 14:47:53

In this example, the stored procedure was created on March 15, 2024, and last modified on June 2, 2025.

Understanding the Results

• create_date: The date and time when the stored procedure was originally created.
• modify_date: The date and time when the stored procedure was last altered.

Limitations
While the modify_date column tells you when a stored procedure was last changed, it does not provide details about:

• The user who made the change
• The client machine used
• The application used to perform the modification
• The actual T-SQL statement that was executed

As a result, this information is useful for identifying the timing of a change but not for determining responsibility. Although it does not provide a complete audit trail, it offers a quick and simple way to confirm whether a stored procedure has been modified and when the change occurred.

Find Who Modified a Stored Procedure

To capture details such as the login name, hostname, and modification activity, you'll need additional auditing mechanisms such as SQL Server Audit, Extended Events, or DDL Triggers, which we'll discuss in the following sections.

Using SQL Server Audit

If you need to identify who modified a stored procedure, when the change occurred, and potentially which client machine initiated the change, SQL Server Audit is one of the most reliable and enterprise-ready solutions available.

Unlike the modify_date column, which only records the last modification timestamp, SQL Server Audit can capture detailed information about database activity, including schema changes such as ALTER PROCEDURE, CREATE PROCEDURE, and DROP PROCEDURE.

Why Use SQL Server Audit?

SQL Server Audit provides several advantages:

• Built-in auditing framework
• Low performance overhead
• Secure and tamper-resistant audit records
• Supports compliance and governance requirements
• Captures user activity and object modifications
• Can log events to files, the Windows Security Log, or the Windows Application Log

For production environments, SQL Server Audit is generally preferred over custom auditing solutions because it is easier to manage and designed specifically for auditing purposes.

Creating a Server Audit

The first step is to create a Server Audit that defines where audit records will be stored.

CREATE SERVER AUDIT ProcedureChangeAudit
TO FILE
(
FILEPATH = 'D:\SQLAudit\'
);
GO
ALTER SERVER AUDIT ProcedureChangeAudit WITH (STATE = ON);
GO

This configuration stores audit events in files located in the specified directory.

Creating a Database Audit Specification

Next, create a database audit specification to capture schema changes.

USE YourDatabase;
GO
CREATE DATABASE AUDIT SPECIFICATION ProcedureAuditSpec
FOR SERVER AUDIT ProcedureChangeAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP);
GO
ALTER DATABASE AUDIT SPECIFICATION ProcedureAuditSpec
WITH (STATE = ON);
GO

The SCHEMA_OBJECT_CHANGE_GROUP action group captures events related to the creation, modification, and deletion of database objects, including stored procedures. For example if someone alters usp_GetSalesReport procedure then SQL Server Audit records information about the modification event.

Reading Audit Records

To view the captured audit data:

SELECT
	event_time,
	action_id,
	server_principal_name,
	database_name,
	object_name,
	statement
FROM sys.fn_get_audit_file ( 'D:\SQLAudit\*.sqlaudit', DEFAULT, DEFAULT )
ORDER BY event_time DESC;

Information Captured
Depending on your audit configuration, you may see

Column	Description
event_time	When the change occurred
server_principal_name	Login that performed the action
database_name	Database containing the object
object_name	Stored procedure name
statement	Executed T-SQL statement
session_server_principal_name	Original login
application_name	Application used for the change
client_ip	Client IP address (when available)

This information makes it much easier to determine who made the change and investigate unexpected modifications.

Performance Considerations

One of the main reasons SQL Server Audit is widely adopted is its minimal impact on database performance.

Audit events are generated only when the configured actions occur. Since stored procedure modifications happen far less frequently than normal application transactions, the performance overhead is typically negligible.

However, as with any auditing solution, it is important to:

• Audit only the events you need
• Monitor audit file growth
• Archive old audit files regularly
• Store audit files on appropriate storage

For organizations that require a secure and maintainable auditing solution, SQL Server Audit is often the preferred method for tracking stored procedure modifications in SQL Server.

Using Extended Events

Extended Events is a lightweight performance monitoring and troubleshooting framework built into SQL Server. It allows database administrators to capture detailed information about database activities, including schema changes such as stored procedure modifications.

If SQL Server Audit is not available or you need a more flexible monitoring solution, Extended Events can be used to track when stored procedures are created, altered, or dropped, along with information about the user and application responsible for the change.

Why Use Extended Events?

• Lightweight and efficient
• Built into SQL Server
• Highly configurable
• Minimal performance impact
• Captures detailed event information
• Suitable for production environments

Unlike custom logging solutions, Extended Events is designed specifically for monitoring SQL Server activities and can capture a wide range of database events with very low overhead.

Creating an Extended Events Session

The following example creates an Extended Events session to capture object alteration events:

CREATE EVENT SESSION TrackProcedureChanges
ON SERVER
ADD EVENT sqlserver.object_altered
(
  ACTION
  (
    sqlserver.client_app_name,
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.server_principal_name,
    sqlserver.sql_text
  )
)
ADD TARGET package0.event_file
(
	SET filename = 'D:\XEvents\ProcedureChanges.xel'
);
GO

This session captures:

• Login name
• Client hostname
• Database name
• Application name
• Executed T-SQL statement

Enable the Session

After creating the session, enable it:
ALTER EVENT SESSION TrackProcedureChanges
ON SERVER
STATE = START;
GO

Once started, SQL Server begins collecting events whenever database objects are modified.

Viewing Captured Events

SELECT
event_data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file
(
'D:\XEvents\ProcedureChanges*.xel', NULL, NULL, NULL
);

Information Captured
Depending on the configured actions, Extended Events can capture

Information	Description
Timestamp	When the change occurred
Server Principal	Login that performed the action
Database Name	Database containing the object
Client Hostname	Machine that initiated the change
Application Name	Tool used for the change (SSMS, application, etc.)
SQL Text	Executed ALTER PROCEDURE statement
Object Name	Name of the modified object

This information can help identify both the user and the source machine responsible for the modification.

When Should You Use Extended Events?

• Lightweight monitoring
• Real-time troubleshooting
• User and host tracking
• Detailed event analysis
• Minimal performance impact

For many DBAs, Extended Events provides an effective way to track stored procedure changes without implementing custom auditing solutions or relying solely on SQL Server Audit.

Performance Considerations

One of the biggest advantages of Extended Events is its low overhead.

Compared to older technologies such as SQL Trace and SQL Server Profiler, Extended Events is significantly more efficient and is Microsoft's recommended monitoring framework for modern SQL Server environments.

Because schema changes occur relatively infrequently, capturing events such as object_altered generally has a negligible impact on system performance.

SQL Server Audit vs. Extended Events

While both technologies can track stored procedure modifications, they serve slightly different purposes:

Feature	SQL Server Audit	Extended Events
Compliance Auditing	Excellent	Limited
Performance Monitoring	Basic	Excellent
Flexibility	Moderate	High
Overhead	Low	Very Low
Security Focus	Strong	Moderate
Troubleshooting	Good	Excellent

Using DDL Triggers

DDL (Data Definition Language) Triggers provide a custom auditing mechanism for tracking schema changes in SQL Server. Unlike SQL Server Audit or Extended Events, which are built-in monitoring solutions, DDL Triggers allow you to create your own audit process and store change information in a table for future analysis.

A DDL Trigger can be configured to fire whenever a database object is created, modified, or dropped. This makes it a practical option for tracking stored procedure changes when built-in auditing features are not available or when organizations require a custom audit trail.

How DDL Triggers Work

When a user executes a statement such as:

  
  ALTER PROCEDURE dbo.usp_GetSalesReport

SQL Server fires the DDL Trigger before completing the operation. The trigger can then capture information about the event and store it in an audit table.

Typical information collected includes:

• Login name
• Host machine name
• Event type
• Object name
• Database name
• Timestamp
• Executed command details

Creating an Audit Table

  CREATE TABLE dbo.ProcedureAudit
(
  AuditID INT IDENTITY(1,1) PRIMARY KEY,
  AuditDate DATETIME,
  LoginName SYSNAME,
  HostName SYSNAME,
  EventType VARCHAR(100),
  ObjectName SYSNAME,
  DatabaseName SYSNAME
);
GO

Creating the DDL Trigger

CREATE TRIGGER trg_ProcedureAudit
ON DATABASE
FOR
  CREATE_PROCEDURE,
  ALTER_PROCEDURE,
  DROP_PROCEDURE
AS
BEGIN
  DECLARE @EventData XML = EVENTDATA();
  INSERT INTO dbo.ProcedureAudit
  (
    AuditDate,
    LoginName,
    HostName,
    EventType,
    ObjectName,
    DatabaseName
  )
  SELECT
        GETDATE(),
        ORIGINAL_LOGIN(),
        HOST_NAME(),
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
        DB_NAME();
END;
GO

Once enabled, the trigger automatically records procedure creation, modification, and deletion activities.

Viewing Audit Records

SELECT * FROM dbo.ProcedureAudit ORDER BY AuditDate DESC;

Example output:

AuditDate				LoginName	HostName		EventType		ObjectName
---------------------------------------------------------------------------------
2025-06-02 	14:47:53	DBAUser		DEV-PC01	ALTER_PROCEDURE usp_GetSalesReport

This information helps identify who made the change and from which machine it originated.

Performance Considerations

One of the most common concerns about DDL Triggers is performance impact.

In most environments, the impact is minimal because the trigger executes only during schema changes such as:

• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE

These operations occur far less frequently than normal application activities such as SELECT, INSERT, UPDATE, and DELETE statements.

However, performance issues can arise if the trigger:

• Performs complex queries
• Writes large amounts of data
• Calls external resources
• Contains inefficient logic

For best results, keep the trigger lightweight and focused on recording essential audit information.

When Should You Use DDL Triggers?

DDL Triggers are a good choice when:

• SQL Server Audit is unavailable
• A custom auditing solution is required
• You need to store audit information in database tables
• Auditing requirements are limited to specific objects or databases

For enterprise environments requiring compliance-grade auditing, SQL Server Audit is usually the preferred solution. However, DDL Triggers remain an effective and flexible option for tracking stored procedure changes and identifying the users and client machines responsible for those changes.

Using the Default Trace (Legacy Option)

If auditing was not configured before the stored procedure was modified, you may still be able to find evidence of the change using SQL Server's Default Trace.

The Default Trace is a built-in trace that captures various server and database events, including some schema modifications. In certain cases, it can help identify:

• When an object was modified
• Which login performed the change
• The client hostname
• The application used

However, there are important limitations:

• The Default Trace stores only a limited history of events.
• Older events are automatically overwritten.
• The feature may be disabled on some servers.
• It is considered a legacy solution and should not be relied upon for long-term auditing.

For these reasons, the Default Trace is best viewed as a forensic troubleshooting tool rather than a dedicated auditing solution.

Before querying the trace, verify that it is enabled:

SELECT * FROM sys.traces WHERE is_default = 1;

If the Default Trace is available and the modification occurred recently, it may provide valuable information about the stored procedure change. For ongoing monitoring and auditing, SQL Server Audit or Extended Events remain the recommended approaches.

Will DDL Triggers Impact Performance?

One of the most common concerns when implementing DDL Triggers is whether they will negatively affect SQL Server performance. The short answer is yes, there is some overhead, but in most environments the impact is negligible when the trigger is designed properly.

Unlike DML triggers, which execute during data modification operations such as INSERT, UPDATE, and DELETE, DDL Triggers fire only when schema changes occur. Examples include:

• CREATE PROCEDURE
• ALTER PROCEDURE
• DROP PROCEDURE
• CREATE TABLE
• ALTER TABLE

Since schema modifications are typically infrequent compared to regular application transactions, DDL Triggers usually have little impact on day-to-day database performance.

Why the Impact Is Usually Minimal

A DDL Trigger executes only when a database object is created, modified, or removed. In most production environments, these operations occur during deployments, maintenance activities, or controlled change windows rather than during normal application usage.

For example, a trigger that simply records the following information into an audit table:

• Login name
• Host name
• Object name
• Event type
• Timestamp

will generally add only a small amount of overhead to the schema modification operation.

When Performance Issues Can Occur

A DDL Trigger executes only when a database object is created, modified, or removed. In most production environments, these operations occur during deployments, maintenance activities, or controlled change windows rather than during normal application usage.

Performance problems are more likely when the trigger performs excessive processing, such as:

• Running complex queries
• Accessing large tables
• Calling external resources
• Sending emails or notifications
• Writing large amounts of audit data
• Performing extensive XML parsing

Because DDL Triggers execute within the same transaction as the schema change, inefficient trigger code can increase the time required to complete the operation.

Best Practices to Minimize Impact

To keep DDL Trigger overhead low:

• Capture only the information you need.
• Avoid complex business logic inside the trigger.
• Store audit records in a dedicated table.
• Keep INSERT operations simple and efficient.
• Regularly archive or purge old audit records.
• Test trigger performance in a non-production environment before deployment.

DDL Triggers vs SQL Server Audit

When comparing auditing solutions, SQL Server Audit is generally more efficient and secure because it is implemented as a native SQL Server feature. DDL Triggers provide greater flexibility but require additional development and maintenance.

For organizations with strict compliance requirements, SQL Server Audit is usually the preferred option. However, for custom auditing needs, DDL Triggers remain a practical and effective solution.

Final Verdict

A well-designed DDL Trigger that logs basic auditing information is unlikely to have a noticeable impact on overall database performance because it executes only during schema changes. The key is to keep the trigger lightweight and focused on auditing rather than complex processing.

For most SQL Server environments, DDL Triggers can be safely used to track stored procedure modifications without affecting normal application workloads.

Audit vs Extended Events vs DDL Triggers

SQL Server provides multiple ways to track stored procedure modifications, each with its own strengths and trade-offs. Choosing the right solution depends on your requirements for auditing, performance, security, and maintenance.

The following comparison highlights the key differences between SQL Server Audit, Extended Events, and DDL Triggers.

Feature	SQL Server Audit	Extended Events	DDL Triggers
Purpose	Security and compliance auditing	Monitoring and troubleshooting	Custom change tracking
Tracks Stored Procedure Changes	Yes	Yes	Yes
Captures Login Information	Yes	Yes	Yes
Captures Host Machine	Yes	Yes	Yes
Captures SQL Statement	Yes	Yes	Yes
Performance Impact	Low	Very Low	Low
Security	High	Moderate	Moderate
Customization	Limited	High	Very High
Maintenance Effort	Low	Moderate	High
Long-Term Auditing	Excellent	Good	Good
Compliance Reporting	Excellent	Limited	Limited
Built-in Solution	Yes	Yes	Yes

Which Option Should You Choose?

For most organizations, the recommended approach is:

1. SQL Server Audit – Best for security, compliance, and long-term auditing.
2. Extended Events – Best for lightweight monitoring and troubleshooting.
3. DDL Triggers – Best when custom auditing requirements exist.

If your primary goal is simply to identify who modified a stored procedure and from which machine, all three solutions can accomplish the task. However, for production environments that require reliability, security, and minimal maintenance, SQL Server Audit is generally the preferred choice.

Best Practices for Tracking Stored Procedure Changes

• Use SQL Server Audit whenever available.
• Use Extended Events for lightweight monitoring.
• Keep DDL triggers simple and efficient.
• Store audit information in a dedicated table.
• Regularly archive audit data.
• Maintain stored procedures in source control systems such as Git.

Summary

Tracking stored procedure modifications is essential for maintaining database security, compliance, and operational stability. While SQL Server does not automatically retain information about who changed a stored procedure and from which machine, organizations can implement SQL Server Audit, Extended Events, or DDL Triggers to establish a reliable audit trail.

For most production environments, SQL Server Audit is the preferred approach due to its low overhead, security, and comprehensive tracking capabilities.

Thanks