Audit stored procedures and functions?

Question

Audit stored procedures and functions?

halifaxdal

SSCoach

Points: 19741
More actions
June 2, 2010 at 12:01 pm

#156998

Hi folks,
I am just wondering if there is a way to track the usage of stored procedures and functions in SQL 2005? I want to know how many times an sp or fn was executed and by who?
Another question is how do I know the last modified date of each object?
Thanks.

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

halifaxdal (6/2/2010)
Hi folks,
I am just wondering if there is a way to track the usage of stored procedures and functions in SQL 2005? I want to know how many times an sp or fn was executed and by who?
Another question is how do I know the last modified date of each object?
Thanks.

to track usage, I think you'll need to add a server side DML trace, which you can then query over time to check for usage; iusage or counts of usage are not part of the "default" trace.

to check on created/modified, the view sys.objects ahs what you need:

select name,create_date,modify_date from sys.objects WHERE type_desc in

('SQL_STORED_PROCEDURE',

'AGGREGATE_FUNCTION',

'SQL_INLINE_TABLE_VALUED_FUNCTION',

'SQL_TABLE_VALUED_FUNCTION',

'SQL_SCALAR_FUNCTION')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Thank you.

to check on created/modified, the view sys.objects ahs what you need:
select name,create_date,modify_date from sys.objects WHERE type_desc in
('SQL_STORED_PROCEDURE',
'AGGREGATE_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_SCALAR_FUNCTION')

halifaxdal SSCoach Points: 19741 More actions · Answer 3

One thing missing here: is it possible to find out the last modified user? who made the last change? It seems not included in the sys.objects table. I do see a priciple_id in sys.objects however all are NULL in my result, I am not sure if that represents the user who did the last update to the object.

Lowell SSC Guru Points: 323546 More actions · Answer 4

halifaxdal (6/2/2010)
One thing missing here: is it possible to find out the last modified user? who made the last change? It seems not included in the sys.objects table. I do see a priciple_id in sys.objects however all are NULL in my result, I am not sure if that represents the user who did the last update to the object.

if not too much time has elapsed, the default trace might have the info for last created/modified...but it can roll over and overwrite on a server with a lot of DDL stuff going on.

easiest way is to use the built in GUI report, which auto filters on the selected db:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

halifaxdal SSCoach Points: 19741 More actions · Answer 5

thank you Lowell

Where can I find the report file (.rdl?)? maybe from the report file I can find some clue on how the report is generated and create my own customized report.