Post reply

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

  • October 25, 2011 at 11:06 am

    #245217

    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?

  • October 25, 2011 at 12:06 pm

    #1399250

    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]

  • October 25, 2011 at 12:34 pm

    #1399260

    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

  • October 25, 2011 at 1:10 pm

    #1399272

    Thanks guys,

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

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