Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    23

    Unanswered: How to check whether stored procedure is being used

    Hi,

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    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.

  4. #4
    Join Date
    Mar 2008
    Posts
    23
    those stored procedures like sp_add_job and sp_start_server which deals with the sql agent.. should i remove the public for these? any suggestion?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •