Need to find the user who modified a particular stored procedure.

  • ALIF-662928

    SSCertifiable

    Points: 6054

    I need to find the user name of the person who modified a particular stored procedure.

    How do I find out when a stored procedure was last modified or compiled?

    gives me idea about the time. But how do I know the user who modified it?

  • Ben Teraberry

    SSChampion

    Points: 11815

    You can find out some info on your proc through the DMV sys.dm_exec_procedure_stats. The cached_time field would give you an idea of when it was changed because it would have been recompiled. However, it's possible that other conditions would have caused a subsequent recompile later, so it's a loose guide at best.

    To know who made the change (or to have a more accurate idea of when the change was made) you would need to have DDL triggers set up to capture data when objects are changed. This is not done automatically.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004474

    The only way to tell who changed it, if you don't have some custom auditing is to check the default trace, assuming the record of the modification is still in there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ALIF-662928

    SSCertifiable

    Points: 6054

    Thanks guys,

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

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