Logging stored procedures execution and/or modification?
-
Hello. (Pretty much noob here.)
Is there a way to log (or maybe there's some "hidden" table) changes or executions of stored procedures? Preferably also who performed a change and what did he/she change. We need this so we can potentially blame someone if anything goes wrong ;).
-
Profiler is the only thing available.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
If you want to track changes to stored procedures you can create a DDL trigger for ALTER PROCEDURE, I assume you are on 2005 since this is a 2005 forum, that logs the change. LIke this:
[font="Courier New"]CREATE TRIGGER [proc_changes] ON DATABASE
FOR
create_procedure, alter_procedure, drop_procedure
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO dblog.dbo.changelog
(
databasename,
eventtype,
objectname,
objecttype,
sqlcommand,
loginname
)
VALUES
(
@data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''VARCHAR(256)''),
@data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''VARCHAR(50)''),
@data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''VARCHAR(256)''),
@data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''VARCHAR(25)''),
@data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''VARCHAR(MAX)''),
@data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''VARCHAR(256)''))
ENABLE TRIGGER [proc_changes] ON DATABASE[/font]
I don't know anyway to log execution of an SP from outside the SP other than profiler.
There should be a limited number of people who have rights to create and alter procedures, and on a production box it really should only be the DBA responsible for the box.
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 -
DDL triggers are good for finding stored proc changes.
If you want data changes from a specific proc, Profiler or a server-side trace. You could log things into another database.
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 -
okkko (6/4/2008)
Hello. (Pretty much noob here.)Is there a way to log (or maybe there's some "hidden" table) changes or executions of stored procedures? Preferably also who performed a change and what did he/she change. We need this so we can potentially blame someone if anything goes wrong ;).
Depending on what you are calling changes - you might care to check out the "Schema changes history" report (available on right-click after you install SP2). It tracks DDL changes to your objects (so not data changes, but changes to table structures, modifications to SP's, etc....).
It's based on the "default profiler trace" which is on by default on a SQL 2005 installation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Changes, use DDL triggers.
Executions, either write your own logging Sprocs and include them or constantly trace for SP:Starting
-
Oh man, thanks for all the replies. I'm currently not at work but will look into your suggestions. Thanks every one!
-
....
Depending on what you are calling changes - you might care to check out the "Schema changes history" report (available on right-click after you install SP2). It tracks DDL changes to your objects (so not data changes, but changes to table structures, modifications to SP's, etc....).
It's based on the "default profiler trace" which is on by default on a SQL 2005 installation.
Sorry :ermm: where do I right click what? 😀 thanks
-
Jack Corbett (6/4/2008)
If you want to track changes to stored procedures you can create a DDL trigger for ALTER PROCEDURE, I assume you are on 2005 since this is a 2005 forum, that logs the change. LIke this:code..
I don't know anyway to log execution of an SP from outside the SP other than profiler.
There should be a limited number of people who have rights to create and alter procedures, and on a production box it really should only be the DBA responsible for the box.
I see that the Profiler needs to run the whole time, right? So, this isn't what I'm looking for.
I created the trigger like you suggested (had to change the double quotes to mono quote). However, I don't know what is this dblog.dbo.changelog table nor where is located or how to access/use it. It seems that the server itself can't locate it since I tried to create a simple stored procedure and I got this error:
"Msg 208, Level 16, State 1, Procedure proc_changes, Line 13
Invalid object name 'dblog.dbo.changelog'."
Help? Btw, what Sql Server 2005 book do you recommend?
-
okkko -
I think Jack didn't include the part where he specifically built a "changelog" table somewhere. It actually looks like there's a utility database called dblog where he's tracking that stuff.
You would have to build a similar table somewhere (in your own utility database, or in a changelog table within the database you're working on). Just adjust your reference to it when you have it built...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
okkko (6/5/2008)
Help? Btw, what Sql Server 2005 book do you recommend?*shameless self promotion* For the question you asked here? The book in my sig. I wrote the chapter on DDL triggers and cover how to do this.
K. Brian Kelley
@kbriankelley -
chileu17 (6/4/2008)
Sorry :ermm: where do I right click what? 😀 thanksIf you're using SSMS, right-click on the database itself. That'll bring up a pop-up menu which has a Reports option. It's pulling the data from the default profiler trace that's being written to the LOG directory of your SQL Server instance.
K. Brian Kelley
@kbriankelley -
K. Brian Kelley (6/5/2008)
chileu17 (6/4/2008)
Sorry :ermm: where do I right click what? 😀 thanksIf you're using SSMS, right-click on the database itself. That'll bring up a pop-up menu which has a Reports option. It's pulling the data from the default profiler trace that's being written to the LOG directory of your SQL Server instance.
True...except for the Schema Change History report. That's one you apparently need to access from the SERVER name. So - same procedure - just right-click on the SERVER name instead of on a specific DB.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt Miller (6/5/2008)
True...except for the Schema Change History report. That's one you apparently need to access from the SERVER name. So - same procedure - just right-click on the SERVER name instead of on a specific DB.The Schema Changes History report understands where you are running it from. If you right-clicked at the server level, you get all the schema changes for the whole server. However, if you right-click on a specific database, it'll only pull changes for that database.
K. Brian Kelley
@kbriankelley -
hmm... and it takes the keeper of the Duck to make me notice it on BOTH lists....I somehow ahdn't noticed it there previously....
Thanks Brian!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply