Identifying if a user/client changes a stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Identifying if a user/client changes a stored...

Identifying if a user/client changes a stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Stevie T

Posted Thursday, November 29, 2007 8:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 07, 2012 6:43 AM
Points: 49, Visits: 94

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.

Post #427496

bdavey

Posted Thursday, November 29, 2007 8:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:03 AM
Points: 44, Visits: 236

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

Post #427530

John Esraelo-498130

Posted Friday, November 30, 2007 8:37 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

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

Post #428143

Steve Jones - SSC Editor

Posted Friday, November 30, 2007 9:03 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 9:24 AM
Points: 31,433, Visits: 13,748

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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #428167

bdavey

Posted Friday, November 30, 2007 9:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 10:03 AM
Points: 44, Visits: 236

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
-----------------------------------------------------------------------------------

Post #428191

John Esraelo-498130

Posted Friday, November 30, 2007 10:07 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:10 PM
Points: 518, Visits: 920

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.990 dbo CREATE_TABLE CREATE TABLE TestTable (a int)
2007-11-30 09:05:00.037 dbo DROP_TABLE DROP TABLE TestTable ;

good one though

Cheers,
John Esraelo

Post #428214

« Prev Topic | Next Topic »

Permissions