Identify individual sql user in SQL profiler And activity monitor

Question

We're using SQL 2005 and want to identify individual users so we can trace their SQL for performance purposes, but we're finding it hard to unique identify who is who.

We're using SQL 2005 with connection pooling so every user has the same user in the Activity Monitor. Their NT user name doesn't appear to be set - maybe because we're using SQL Server users not domain users, we're also using Citrix so there is no individual IP address set. In this (very common) environement how do you identify an individual user?

Answer 1

If everyone is using the same username and coming from the same IP you won't be able to tell them apart. Unless you're using windows authentication MSSQL won't even be told what username on windows the user connecting has.

Have you considered changing your setup so that it uses Windows Authentication? It seems like the logical solution to the problem. Either that or setting up separate logins for everyone on SQL but that'd be duplicating your Active Directory user list...

Answer 2

In order to identify users in SQL Profiler, you need to provide that information to SQL Server in some way with each request or as part of the connection context. One way is to connect as different users, but if you have thousands of users, you would need thousands of accounts (SQL Auth or Windows Auth), and it becomes unmanageable quickly.

A much better way is to set the Application Name parameter in the connection string to be the name of the user. Once set, you can filter on that field in SQL Profiler: Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;Application Name=RickNZ

The disadvantage with this approach is that connection pooling (which is enabled by default) only shares connections when the connection strings are byte-for-byte identical. So if you make them different per user, then you will have many more connections, with a resulting impact on performance. In a heavily multi-threaded environment, there's also a possibility that you could run out of available pooled connections. Even so, it might be useful for short-term debugging.

Answer 3

Solved the problem by tracking the Client Process Id in Sql Profiler. We can identify a particular user and their PID from within Citrix, or Task Manager in a normal setup. Then filter the output in Sql Profile by that PID.

This is brilliant when you're working on a DB but don't have access to the source of the application. Often standard reports need to be changed, SP's fixed etc, but if you don't know what's being run it's a needle in a haystack - use Sql Profile to track a user, capture the Sql, analyse/debug - fix move on.