Post reply

Trace login + db names

  • July 6, 2016 at 2:50 am

    #328090

    Hi all,

    We need to run traces to determine which loginnames are using which databases across our estate.

    Which template or events need to be selected? I've tried the Security Audit event group, but that doesn't capture the DB name, which is the most important info!!

    Thanks,

    JB

  • July 6, 2016 at 2:55 am

    #1888632

    Add the DB name from the (Global Fields) Actions tab, it won't be default part of the event's columns

    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

  • July 6, 2016 at 3:00 am

    #1888633

    Can't find that tab, probably because I should have put this in the SQL 2008 section, apologies 🙁

  • July 6, 2016 at 3:05 am

    #1888636

    2008, so you're using server-side traces?

    The Security Audit: Audit Login has both database id and database name as event columns. Just check the boxes when you create the trace, they're not checked by default.

    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

  • July 6, 2016 at 6:38 am

    #1888663

    Thanks, another if I may...

    I've now defined the desired events/filters, and want to run the same trace definition from server to server (and this may cover servers pre and post 2008), is it a case of exporting trace definitions on Server 1, dropping the file onto a shared folder and then from Server 2 importing definitions from that file? I'm unsure of the procedure.

    Edit: I'd like to do this in the simplest way possible.

    Thanks

  • July 6, 2016 at 7:32 am

    #1888680

    If it's server-side traces that you're using, then the easiest thing is to use Profiler to set up the trace, then go to File -> Export -> Script Trace Definition

    That gives you the T-SQL for the server-side trace, you can then use that in job steps on multiple servers to run the traces.

    DO NOT run traces from Profiler on production servers, it can and does cause lots of problems, up to and including server crashes.

    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 6 posts - 1 through 5 (of 5 total)

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