I would like to be able to trace who or what process made a change to a stored procedure
-
I would like to be able to trace who or what process made a change to a stored procedure. I am using the following script which identifies the date the SP was modified.
SELECT *
FROM sys.all_objects
WHERE type = 'p'
and name = 'USP_MOD_VOLUNTEER_LEADERSHIP_MEMBER_DETAIL'
This query returns the following columns Name, Object_id, Principal, schema_id, parent_obeject_id, type, type_desc, create_date, modify_date, is_ms_shipped_is_ms_published, is_schema_published.
Has any been able to extend this query so you can view who or what process triggered the modifications
-
the default trace keeps a short list of recent changes;
if not too much time has passed, you can find out who did any changes and when:
easiest way is directly from the GUI:
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! -
Here's a "poor man's source control" I wrote/found/modified a few years back. (In all honesty, I can't recall how much of this was "found" on the internet and how much I wrote, so pardon me if I'm not giving proper credit here. 😀
Also, I'm planning on putting together an article to submit here with more details....
This not only holds the info you're looking for, but a complete change history as well.
1. Create a table to hold the info.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_sourceControl](
[nDex] [int] IDENTITY(1,1) NOT NULL,
[eventType] [nvarchar](50) NOT NULL,
[postTime] [datetime] NOT NULL,
[SPID] [int] NOT NULL,
[serverName] [nvarchar](128) NOT NULL,
[loginName] [nvarchar](128) NOT NULL,
[userName] [nvarchar](128) NULL,
[databaseName] [nvarchar](128) NULL,
[schemaName] [nvarchar](128) NULL,
[objectID] [int] NULL,
[objectName] [nvarchar](128) NULL,
[objectType] [nvarchar](128) NOT NULL,
[hostName] [nvarchar](128) NOT NULL,
[TSQL] [nvarchar](max) NOT NULL,
[xmlEvent] [xml] NULL
CONSTRAINT [PK__sourceControl_NDEX] PRIMARY KEY CLUSTERED
(
[nDex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
Now create the DDL trigger on the target DB. *Note, there is some issue I can't recall at the time that comes up with linked servers and XML. If this is all on the same machine, you'll be fine.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_sourceControl_DBTrigger]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS, DDL_FUNCTION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TRIGGER_EVENTS
AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @objectID INT;
DECLARE @eventType sysname;
DECLARE @SqlText NVARCHAR(MAX);
DECLARE @objectType NVARCHAR(128);
DECLARE @spid INT;
DECLARE @serverName NVARCHAR(128);
DECLARE @loginName NVARCHAR(128);
DECLARE @databaseName NVARCHAR(128);
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int');
SET @objectType = @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(128)')
SET @serverName = @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(128)');
SET @loginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)');
SET @SqlText = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
SET @databaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)');
IF @object IS NOT NULL
BEGIN
SELECT
@objectID = object_id
,@objectType = type_desc
FROM
sys.objects
WHERE
[NAME] = CONVERT(sysname, @object)
IF ISNULL(@schema, '') = ''
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + @object;
ELSE
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + ISNULL(@schema + '.', '') + @object;
END
ELSE
BEGIN
SET @objectID = 0
PRINT 'Event Logged: ' + ISNULL(@eventType, '') + ' - ' + ISNULL(@schema + '.', '');
END
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
IF @object = 'ddlDatabaseTriggerLog' OR @eventType = 'CREATE_STATISTICS'
RETURN
-----------
INSERT YOURDB.YOURSCHEMA.YOURTABLENAME
([eventType]
,[postTime]
,[SPID]
,[servername]
,[loginName]
,[userName]
,[databaseName]
,[schemaName]
,[objectID]
,[objectName]
,[objecttype]
,[hostName]
,[TSQL]
,[xmlEvent])
VALUES
(
@eventType,
GETDATE(),
@spid,
@serverName,
@loginName,
CURRENT_USER,
@databaseName,
CONVERT(sysname, @schema),
@objectID,
CONVERT(sysname, @object),
@objectType,
CONVERT(NVARCHAR(128), HOST_NAME()),
@SqlText,
);
END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
DISABLE TRIGGER [ddl_sourceControl_DBTrigger] ON DATABASE
GO
ENABLE TRIGGER [ddl_sourceControl_DBTrigger] ON DATABASE
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply