Post reply

query to get the user that created the stored procedure

  • October 12, 2011 at 4:16 am

    #100423

    hi community,

    is there a query that display the user that created the stored procedure? (ex. domainname\user)

    I tried sysobjects but there's no column for that.

    thanks in advance

  • October 12, 2011 at 8:02 pm

    #1393758

    any help guys? i really need to know this, because i have to submit a report that list down all stored proc with the user that created it.

  • October 13, 2011 at 12:08 am

    #1393814

    That information doesn't exist unless you had a trace running at the time the procedure was created. SQL does not track it at all by default. (and before someone suggests default trace\, please note the SQL 2000 forum...)

    p.s. Be patient. This is a discussion forum, not a chat room, there's no SLA here and half of us are at a major SQL conference this week

    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 13, 2011 at 12:20 am

    #1393820

    sorry for that, the audit is for our sql server 2000 🙁

  • October 13, 2011 at 12:59 am

    #1393829

    so far, i found this, dbcc log, but not sure if i can get an info regarding user that created or modify stored procedure.

  • October 13, 2011 at 8:04 am

    #1394039

    That reads the active portion of the transaction log. While there is a free log reader tool for SQL 2000, all you'll be able to get is the database user (not login) that created any procedures since the last log backup (in full recovery) or checkpoint (in simple)

    This information is simply not tracked at all in SQL 2000 and is only minimally tracked in other versions. What you're asking for, you cannot get.

    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 13, 2011 at 8:34 pm

    #1394425

    i think that would be enough for the report, thanks for the help 🙂

  • October 13, 2011 at 11:08 pm

    #1394443

    Teemo (10/13/2011)

    i think that would be enough for the report, thanks for the help 🙂

    What? Users since the last log backup (probably less than an hour depending on log backup frequency) or checkpoint (last couple of minutes)?

    If you're looking at that kind of time frame, you can set up a trace to do a proper and complete job going forward.

    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

Viewing 8 posts - 1 through 7 (of 7 total)

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