February 18, 2009 at 8:29 am
i need help with setting up a trace. I am haveing a problem with someone changing stored procs in a database i maintain. i want to set up a trace to let me know when someone changes a stored proc.
can anyone point me in the right direction please.
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
February 18, 2009 at 8:39 am
If you are using SQL 2005 (I assume you are) you can look at DDL Triggers.
CREATE TRIGGER AuditProc
ON DATABASE
FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
DECLARE @event XML
SET @event = EVENTDATA()
INSERT INTO AuditTable (ChangeTime, , ObjectDesc, LoginID)
VALUES
(
GetDate(),
@event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@event.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
Hope this helps
-Roy
February 18, 2009 at 8:56 am
yes i am useing 2005
is there prep work for this to work?
also i got these 2 error msgs
Msg 1084, Level 15, State 1, Procedure AuditProc, Line 3
'CREATE_PROC' is an invalid event type.
Msg 102, Level 15, State 1, Procedure AuditProc, Line 9
Incorrect syntax near ','.
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
February 18, 2009 at 9:00 am
On SQL Server 2005 you could query the default trace which audits object altered, created, deleted with a query like this:
SELECT
TE.[name] AS event_name,
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
I.StartTime,
I.StartTime,
I.ObjectID,
OBJECT_NAME(I.ObjectID) AS NAME,
I.ObjectName,
I.ObjectType,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
:: fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1,
CHARINDEX('_', T.[path]) - 1) +
'.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON I.EventClass = TE.trace_event_id LEFT JOIN
sys.server_principals S
ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.DatabaseName = '[your db name]' AND
-- you can use the event
TE.[name] = 'Object:Altered'
-- or you could use this if you are only worried about one object
--I.Object_Name = '[your object name]'
Since the default trace is running you do not have to have the overhead of the DDL trigger. Just remember that the default trace keeps up to 5 20MB files and that restarts cause a new file to be created so if you have a very busy server you may not have data that far back.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 9:00 am
Sorry , My bad. It is Create_procedure. You can see more detail in BOL.
-Roy
February 18, 2009 at 9:02 am
It's CREATE_PROCEDURE
http://msdn.microsoft.com/en-us/library/ms189871.aspx
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
February 18, 2009 at 9:06 am
Is this a production database they are making changes in? If it is you should setup a dev system that they make changes in and then do not give them alter procedure rights in the production system so you have to approve code and migrate it to production.
If you want notifications you can do the DDL triggers or you could do a job that queries the default trace on a regular basis which sends you an email when a procedure is altered.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 9:15 am
no it's not a production system (heads would really be rolling if it was) this is the dev/testing server.
i think (but can't prove yet) that the testers are (for some strange reason) grabbing older versions of stored procs from our version control and executing them there for over writeing the current stored proc. I am trying to track down whom this is.
i'll look into the creating a job idea...
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
February 18, 2009 at 9:50 am
Jack I get an error when trying to test the code snippet you pasted...
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'CASE'.
I must be blind, because I'm not sure what he error is...did the forum softwar rip out a parenthesis or something?
Lowell
February 18, 2009 at 10:01 am
Runs fine against my server. Try replacing the "::" with "sys." as I recall having recently read where the "::" syntax is being deprecated.
I have seen odd errors like this when using fn_trace_gettable at times. You could just query sys.traces for the path to the default trace and hard code it in. Then you can remove the reference to sys.traces totally from the query. You just need to make sure you remove the "_nn" part of the path if you want to query all the trace files created by the default trace.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 10:05 am
works for me, double check what you copied and pasted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 18, 2009 at 10:09 am
my fault... i was running the query in a database that was Compatibility level 80....duh!
Lowell (2/18/2009)
Jack I get an error when trying to test the code snippet you pasted...Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'CASE'.
I must be blind, because I'm not sure what he error is...did the forum softwar rip out a parenthesis or something?
Lowell
February 18, 2009 at 10:10 am
Jack
did you mean to specify column I.StartTime twice?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 18, 2009 at 10:33 am
Perry Whittle (2/18/2009)
Jackdid you mean to specify column I.StartTime twice?
Nope. I copied most of the code from a script I use for another purpose that uses Max and Min StartTime.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 10:43 am
Jack
If you want notifications you can do the DDL triggers or you could do a job that queries the default trace on a regular basis which sends you an email when a procedure is altered.
This is interesting, how could you do this.
thanks
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply