November 9, 2021 at 1:01 pm
Over the period of time we created lot of customer stored procedure and views.
We would like to audit which of these views and stored procedures are in use. Is there any way to audit it?
We are using SQL 2005. i am open to options.
November 9, 2021 at 4:25 pm
One approach would be to modify them and add in a logging table to capture when the stored procedure is called.
Another way would be to start a profiler trace and capture the data for a while and then review it.
Depending on how frequently the instance is restarted, you may be able to pull some data from the plan cache to see which stored procedures are in cache and how frequently they are used, but the plan cache will flush things out over time so it isn't really a reliable audit to say how often something is used.
My approach would be option one listed above as then you have a reliable audit that you can review over time to capture usage of stored procedures and you can capture any other data you find relevant (execution time, execution user, etc.). Only downside is it won't have any data in it prior to you modifying the stored procedures and if there are a lot, it could take time to get it set up. BUT an easy approach is to create a "Audit_Log" stored procedure that takes in whatever parameters you are interested in capturing (stored procedure name, parameters passed in, etc.) and then each stored procedure you want to audit just needs to call "Audit_Log" at the start. Makes it scalable and versatile as new stored procedures can add it in and it is only a few extra lines of code.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 12, 2021 at 1:35 pm
Thanks for the reply. How about the views, how we can audit that info?
November 12, 2021 at 5:23 pm
Pretty sure that my advice above would apply to views as well. Profiler or plan cache will capture the TSQL being run and then you can review and audit it.
The Audit Log approach wouldn't work as well as you can't do a trigger on SELECT, but you could do triggers for insert, update, and delete into an audit log. Alternately, you could put a calculated column onto the tables that is just the current timestamp which would handle the insert, and update but wouldn't cover selects or delete. For selects, I think you would need either profiler running OR you would need to investigate the plan cache frequently. Delete would still need a trigger.
I am not sure of a good way otherwise... As a general rule though, I try not to let users or applications do things outside of a stored procedure as then I can create the audit tables on them pretty easily. If an application needs to select data from a table or view, it would call the stored procedure to handle it. Another advantage to this is I can modify the table structure (denormalize or normalize depending on requirements) and the stored procedure can be unaware of the changes. It is unaware because I would take the existing table, break it up into multiple new tables, then create a view with the old table name that joins the data back together how the table used to store it and the stored procedure requires no changes and thus the application requires no changes.
Now, if you allow end users to just run arbitrary TSQL against your system, that is a more difficult beast to tackle. IF the views are only used by SSRS or custom applications, you can review the source code to determine the use. If it is used by other SQL objects, you can use something like SQL Search (free RedGate tool) to evaluate if the view is used in stored procedures and if so, which ones.
Even with the best auditing system you can come up with, there is still risk. What if the view/stored procedure is used once per year and you don't run your audit for a full year? I would recommend doing an audit and documentation on the application layer (SSRS, SSIS, .NET, web services, etc) to mark down all objects used by all systems, then use that data to determine which objects are in use. Then, before dropping any of them, change permissions on the objects and ensure nothing breaks. Let that run with reduced permissions for a while until you are confident nothing breaks (may be a month; may be a year; may never actually go away) and go from there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply