If you’ve ever looked at the output for sys.dm_exec_sessions you may have noticed the column program_name. Frequently you’ll see entries like these:
- .Net SqlClient Data Provider
- Microsoft SQL Server Management Studio
- Microsoft SQL Server Management Studio – Query
- Microsoft SQL Server Management Studio – Transact-SQL IntelliSense
- Microsoft® Windows® Operating System
Basically it’s the program that opened the session. This can be really useful at times. For example making sure that people aren’t logging in using SSMS, or at least finding out if a given session is coming from someone running ad hoc queries through SSMS or running an application.
But the question is, how accurate is this information? Well, for the most part it’s pretty good. The problem is that it’s at once really easy to spoof (change) and on the other hand very rarely changed from the default.
In SSMS it’s as simple as using the Additional Connect Parameters option of the connection screen. Very view people even know how to do this and even fewer are going to bother. However if someone really wants to fool you it isn’t that hard.
To change the application name through a connection string you can simply include “Application Name=MyAppName”. If the developer of an application doesn’t do this you get something generic like .Net SqlClient Data Provider, which just tells you that this is a connection from a .Net provider. Sadly outside of larger software companies I haven’t noticed this used all that often.
So in general the column is nice in that it gives you a general idea of what’s going on, and 95% (IMO) of the time it’s going to at least be fairly accurate. But I wouldn’t rely on it too much.