What is the program_name in sys.dm_exec_sessions?

Leave a comment

January 6, 2020 by Kenneth Fisher

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: