Trace login + db names

Question

Trace login + db names

JaybeeSQL

Hall of Fame

Points: 3501
More actions
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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 4

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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