Identifying if a user/client changes a stored procedure

Question

Identifying if a user/client changes a stored procedure

Stevie T

Right there with Babe

Points: 797
More actions
November 29, 2007 at 8:07 am

#180863

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bdavey Default port Points: 1469 More actions · Answer 1

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

John Esraelo-498130 SSCertifiable Points: 5909 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

If this is 2005, set a DDL trigger on the ALTER STORED PROCEDURE statement and log which stored procedures are changed and by who.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

bdavey Default port Points: 1469 More actions · Answer 4

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

-----------------------------------------------------------------------------------

John Esraelo-498130 SSCertifiable Points: 5909 More actions · Answer 5

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