November 29, 2007 at 8:07 am
I would like to easily be able to identify if a client has changed a stored procedure object. I don't want to wrap/encrypt them as it's useful to diagnose performance problems. Is there a way I can trigger either to write to a table or store some checksum value in the stored proc when it's modified??
Any help would be appreciated.
November 29, 2007 at 8:36 am
You could run a script that would tell you if a SP changed. However this assumes that the person who modified the SP used ALTER and not DROP and CREATE. Below is a sample script you can use.
USE [MyDB]
GO
select * from sys.objects
where type = 'P' and create_date != modify_date
November 30, 2007 at 8:37 am
I could be wrong, but, I don't think this would tell you who did it though, if that was original question's intention..
I like this script, I think I might use it as early as today 🙂 nice..
Cheers,
John Esraelo
November 30, 2007 at 9:03 am
If this is 2005, set a DDL trigger on the ALTER STORED PROCEDURE statement and log which stored procedures are changed and by who.
November 30, 2007 at 9:44 am
Very Cool!! I had no idea. Below is an example I found in the Books Online.
-----------------------------------------------------------------------------------
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/03a80e63-6f37-4b49-bf13-dc35cfe46c44.htm
B. Creating a log table with event data in a DDL trigger
The following example creates a table to store information about all database level events, and populates the table with a DDL trigger. The event type and Transact-SQL statement are captured by using XQuery against the XML data generated by EVENTDATA.
USE AdventureWorks;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Test the trigger.
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
--Drop the trigger.
DROP TRIGGER log
ON DATABASE
GO
--Drop table ddl_log.
DROP TABLE ddl_log
GO
-----------------------------------------------------------------------------------
November 30, 2007 at 10:07 am
this is very cool... although, it shows the db_user and if this is a role and if you have multiple users in that role it could get couldy pretty quick...
2007-11-30 09:04:59.990dboCREATE_TABLECREATE TABLE TestTable (a int)
2007-11-30 09:05:00.037dboDROP_TABLEDROP TABLE TestTable ;
good one though
Cheers,
John Esraelo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply