March 31, 2008 at 8:23 am
Hello all.
I need to find a way to monitor ddl changes in stored procedures in SQL 2000. We've had problems with unauthorized changes being made in replication procedures that cause replication to stop, with no error messages showing up.
Any help or hints would be much appreciated.
TIA
Eric
March 31, 2008 at 9:09 pm
How about adding a trigger in the sysobjects table?
April 1, 2008 at 3:31 am
I didn't think triggers were available on system tables. Am I wrong about this ?
Thanks.
April 1, 2008 at 3:10 pm
ok........they're not really......in Enterprise Mgr it looks like it's going to allow you to create a trigger, but then when you try to, it tells you 'permission denied'. Oh, well.....
Eric
April 2, 2008 at 3:28 pm
Looking at sysobjects with
SELECT name, xtype,base_schema_ver,schema_ver,refdate FROM sysobjects WHERE XTYPE ='p' ORDER BY Name
1. Ran the query
2. Went into the sp and altered same
3. Ran the queriy again
Results
Column Value before altering Value after altering
Name TT_AddKeyWord TT_AddKeyWord
xtype P P
base_shema_ver 32 48
schema_ver 32 48
refdate 2007-10-02 09:01:25.673 2007-10-02 09:01:25.673
Some one correct this posting if I am wrong. Notice the change in the values for base_shema_ver and schema_ver. If these indeed change with each alteration of the stored procedure then one could create a table using a select into, run the SP (Possibly a scheduled agent job) at some interval and then do a comparision of results based on changes in the base_schem_ver and/or schema_ver. This would tell you a change was made but of course not by whom, but if data was collected on a daily basis at least one could get an idea of when.
April 3, 2008 at 11:03 am
Thanks for your input. We're a large shop, and not knowing who made the change still leaves us in a bind. We are moving to 2005 with DDL triggers, but it's a slow process.
April 3, 2008 at 7:57 pm
If you do not have a text file with all your SP's text. This might be an additional aide to store the text of all your SPs. Run this SP from Query Analyzer and either output the results to a text file or expand the procedure to store the results into a database table.. might make recovery time quicker.
CREATE PROCEDURE Dbo.RetrieveAllSPText
AS
DECLARE @Name VARCHAR(128)
DECLARE SpNames CURSOR
FOR SELECT so.name FROM sysobjects so WHERE so.xtype = 'p' AND name NOT LIKE 'dt_%' ORDER BY so.name
OPEN SpNames
FETCH NEXT FROM SpNames INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ROUTINE_SCHEMA AS 'Schema Name', ROUTINE_NAME, ROUTINE_DEFINITION, CREATED From INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME = @Name
FETCH NEXT FROM SpNames INTO @Name
END
CLOSE SpNames
DEALLOCATE SpNames
The following might help you narrow down who might be doing the "bad" thing to your SP
1. Run SP_Who2
2. Select each user's spid.
3. The run DBCC INPUTBUFFER (spid)
Look in the even info column... one short coming, it will only display the first 255 characters of the command. Admittedly sort of a hit or miss method, but the best I can come up with.
Hope it helps
April 3, 2008 at 8:58 pm
You have to believe me - read this article at 10:55 PM.
It appears to be all of what you might need. Give it a try
April 4, 2008 at 10:30 am
Thank you ! I'll definately look into it.
Eric
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply