How to Track and Identify Who Modified a SQL Server Stored Procedure


4 views

When a stored procedure gets modified in SQL Server, the system automatically records this activity in several places. The primary sources we can query are:


-- Check default trace for object modifications
SELECT 
    t.StartTime,
    t.LoginName,
    t.ApplicationName,
    t.HostName,
    te.name AS EventName,
    t.DatabaseName,
    t.ObjectName
FROM sys.fn_trace_gettable(
    (SELECT REVERSE(SUBSTRING(REVERSE(path), 
    CHARINDEX('\', REVERSE(path)), 256)) + 'log.trc'
    FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE te.name LIKE '%Object%'
AND t.ObjectType = 8274 -- Stored Procedures
ORDER BY t.StartTime DESC;

For more detailed tracking, SQL Server Extended Events provide a lightweight monitoring system:


-- Create extended event session to track SP modifications
CREATE EVENT SESSION [TrackSPChanges] ON SERVER 
ADD EVENT sqlserver.object_altered(
    WHERE ([object_type]=(8274))),
ADD EVENT sqlserver.object_created(
    WHERE ([object_type]=(8274))),
ADD EVENT sqlserver.object_deleted(
    WHERE ([object_type]=(8274)))
ADD TARGET package0.event_file(SET filename=N'SP_Changes')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS);
GO

-- Start the session
ALTER EVENT SESSION [TrackSPChanges] ON SERVER STATE = START;
GO

If server audit is configured, you can retrieve modification history:


-- Query server audit data
SELECT 
    event_time,
    server_principal_name,
    database_principal_name,
    object_name,
    statement
FROM fn_get_audit_file('C:\Audits\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'MOD'
AND object_type = 'PROCEDURE'
ORDER BY event_time DESC;

DDL triggers capture schema modifications in real-time. Here's a sample implementation:


-- Create DDL trigger to log stored procedure changes
CREATE TRIGGER tr_audit_sp_changes
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    DECLARE @data XML = EVENTDATA();
    
    INSERT INTO dbo.ProcedureChangeLog (
        EventTime,
        LoginName,
        HostName,
        ApplicationName,
        DatabaseName,
        ObjectName,
        CommandText
    )
    VALUES (
        @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime2'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)'),
        @data.value('(/EVENT_INSTANCE/HostName)[1]', 'nvarchar(128)'),
        @data.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)'),
        @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'),
        @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
    );
END;
GO

-- Create the logging table
CREATE TABLE dbo.ProcedureChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventTime DATETIME2 NOT NULL,
    LoginName NVARCHAR(128) NULL,
    HostName NVARCHAR(128) NULL,
    ApplicationName NVARCHAR(128) NULL,
    DatabaseName NVARCHAR(128) NULL,
    ObjectName NVARCHAR(128) NULL,
    CommandText NVARCHAR(MAX) NULL
);

The plan cache sometimes retains information about recent executions:


-- Check plan cache for recent SP executions
SELECT 
    qs.last_execution_time,
    qt.text AS QueryText,
    qs.execution_count,
    qs.total_logical_reads/qs.execution_count AS AvgLogicalReads,
    qs.last_logical_reads,
    qs.total_elapsed_time/qs.execution_count AS AvgElapsedTime,
    qs.last_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%CREATE PROCEDURE%'
OR qt.text LIKE '%ALTER PROCEDURE%'
ORDER BY qs.last_execution_time DESC;

For comprehensive auditing, implement these measures:

  • Configure SQL Server Audit to track schema changes
  • Enable Change Data Capture (CDC) for critical databases
  • Create a dedicated schema change log table with DDL triggers
  • Regularly archive and analyze audit logs
  • Implement proper permission controls (avoid granting ALTER permissions unnecessarily)

SQL Server provides several native methods to track stored procedure changes:


-- Query the default trace files for DDL changes
SELECT 
    t.StartTime,
    t.DatabaseName,
    t.ObjectName,
    t.ApplicationName,
    t.LoginName,
    t.HostName,
    e.name AS EventName
FROM fn_trace_gettable(
    (SELECT REVERSE(SUBSTRING(REVERSE(path), 
    CHARINDEX('\', REVERSE(path)), 256)) + 'log.trc'
    FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events e ON t.EventClass = e.trace_event_id
WHERE t.EventClass IN (46, 47, 164) -- Object:Altered, Object:Created, Object:Deleted
AND t.ObjectType = 8272 -- Stored procedures
ORDER BY t.StartTime DESC;

For more persistent tracking, implement DDL triggers:


CREATE TABLE dbo.ProcedureChangeAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EventDate DATETIME NOT NULL DEFAULT GETDATE(),
    EventType NVARCHAR(100),
    DatabaseName NVARCHAR(255),
    SchemaName NVARCHAR(255),
    ObjectName NVARCHAR(255),
    UserName NVARCHAR(255),
    OriginalText NVARCHAR(MAX),
    ModifiedText NVARCHAR(MAX),
    HostName NVARCHAR(255),
    ApplicationName NVARCHAR(255)
);

CREATE TRIGGER tr_AuditProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO dbo.ProcedureChangeAudit (
        EventType,
        DatabaseName,
        SchemaName,
        ObjectName,
        UserName,
        OriginalText,
        ModifiedText,
        HostName,
        ApplicationName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(255)'),
        CASE 
            WHEN @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') = 'ALTER_PROCEDURE'
            THEN OBJECT_DEFINITION(OBJECT_ID(
                @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)') + '.' +
                @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')
            ))
            ELSE NULL
        END,
        CASE 
            WHEN @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') = 'ALTER_PROCEDURE'
            THEN @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
            ELSE NULL
        END,
        HOST_NAME(),
        APP_NAME()
END;

For enterprise environments, SQL Server Audit provides comprehensive tracking:


-- Create server audit
USE master;
GO
CREATE SERVER AUDIT ProcedureChangeAudit
TO FILE (FILEPATH = 'C:\Audits\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

-- Enable the audit
ALTER SERVER AUDIT ProcedureChangeAudit
WITH (STATE = ON);
GO

-- Create database audit specification
USE YourDatabase;
GO
CREATE DATABASE AUDIT SPECIFICATION AuditProcChanges
FOR SERVER AUDIT ProcedureChangeAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

For near real-time monitoring of procedure changes:


-- Create extended events session
CREATE EVENT SESSION [TrackProcedureChanges] ON SERVER 
ADD EVENT sqlserver.object_altered(
    WHERE ([object_type]=(8272))),
ADD EVENT sqlserver.object_created(
    WHERE ([object_type]=(8272))),
ADD EVENT sqlserver.object_deleted(
    WHERE ([object_type]=(8272)))
ADD TARGET package0.event_file(
    SET filename=N'C:\XEvents\ProcedureChanges.xel')
WITH (
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF);
GO

-- Start the session
ALTER EVENT SESSION [TrackProcedureChanges] ON SERVER STATE = START;

For teams using source control, integrate with Git/SVN:


-- PowerShell script to auto-commit procedure changes to Git
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=YourServer;Database=YourDB;Integrated Security=True"
$connection.Open()

$command = $connection.CreateCommand()
$command.CommandText = @"
SELECT 
    SCHEMA_NAME(p.schema_id) AS SchemaName,
    p.name AS ProcedureName,
    OBJECT_DEFINITION(p.object_id) AS ProcedureDefinition,
    GETDATE() AS SnapshotDate
FROM sys.procedures p
"@

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataset)

$connection.Close()

$outputPath = "C:\GitRepo\Procedures\"
if (!(Test-Path $outputPath)) {
    New-Item -ItemType Directory -Path $outputPath
}

foreach ($row in $dataset.Tables[0].Rows) {
    $filePath = Join-Path $outputPath "$($row.SchemaName)_$($row.ProcedureName).sql"
    $row.ProcedureDefinition | Out-File $filePath -Encoding UTF8
}

Set-Location $outputPath
git add .
git commit -m "Automated procedure snapshot $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

Several third-party tools provide enhanced auditing capabilities:

  • ApexSQL Audit - Comprehensive tracking with filtering and alerting
  • SQL Compliance Manager - Enterprise-grade auditing solution
  • Redgate SQL Monitor - Includes change tracking features
  • Idera SQL Secure - Security-focused auditing tool