Unanswered: How to check whether stored procedure is being used
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
Without changing the procedure code, you would have to run SQL Profiler to see if anybody is using them, and you would have to run it for a long time since some procedures may only be run once a month, or even just once a year.
If you don't mind editing your procedure code, the attached script will create a usage logging system on your database and the comment section contains some code you can put at the top of your sprocs to record exactly who executed them, when they were executed, and what parameter values were passed.
If it's not practically useful, then it's practically useless.
One warning. We do the same here, with third party security scanning tools. I am not sure if it is the same in your case, but our tool found a few SQL Server system objects, NOT user defined objects, that were accessible by public and flagged them as a security vulnerability. It also added a warning that removing public access from any system objects could break third party apps and SQL Server software itself. So, the tool flagged them as a problem and then indicated that you couldn't do much about them. Try explaining that to an auditor. The point is, be careful of removing permissions to objects just because a 'tool' indicated you should. Be sure you know what you are removing and be sure to test it thoroughly.